append xml nodes

I'm searching for a way to insert nodes from an xml document into an other.
My solution to the problem is something like this:
ALTER PROCEDURE [dbo].[insertOrder]
@.Customer xml (dbo.schemaCustomer),
@.Product xml (dbo.schemaProduct)
AS
BEGIN
declare @.app varchar(1024)
declare @.x xml
set @.app = '<Order><Product>' + CAST(@.Product as varchar(1024)) +
'</Product>'+ CAST(@.Customer as varchar(1024)) +
'<id_order>0</id_order></Order>'
insert into xOrder (doc) values (@.app)
END
but I think there's a better way to solve the problem.
Is it possible to do this witout any casting operation?
Thank you, Luca.
Hello luca,

> I'm searching for a way to insert nodes from an xml document into an
> other. My solution to the problem is something like this:
> ALTER PROCEDURE [dbo].[insertOrder]
> @.Customer xml (dbo.schemaCustomer),
> @.Product xml (dbo.schemaProduct)
> AS
> BEGIN
> declare @.app varchar(1024)
> declare @.x xml
> set @.app = '<Order><Product>' + CAST(@.Product as varchar(1024)) +
> '</Product>'+ CAST(@.Customer as varchar(1024)) +
> '<id_order>0</id_order></Order>'
> insert into xOrder (doc) values (@.app)
> END
> but I think there's a better way to solve the problem. Is it possible
> to do this witout any casting operation? Thank you, Luca.
Not that I've found. Something like this would be ideal if doc was XML-typed
as X1 is here:
declare @.x1 xml,@.x2 xml
set @.x1 = '<pet id="1" ownerID="1"><name>Jack</name></pet>'
set @.x2 = '<descr>Grey<descr>'
set @.x1.modify('insert sql:variable("@.x2") into /pet[1]')
However, the modify method won't work with XML-type variables. Nor does treating
@.x2 as a varchar(x) really get you any place since when you do try that,
you get:
XQuery [modify()]: Only non-document nodes can be inserted. Found "xs:string
?".
..modify() also requires the use of string literal as the query. So, Ick.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
|||Try:
CREATE TABLE xOrder(doc xml)
go
CREATE PROCEDURE [dbo].[insertOrder]
@.Customer xml, --(dbo.schemaCustomer),
@.Product xml --(dbo.schemaProduct)
AS
BEGIN
insert into xOrder (doc) select (
select @.Customer, @.Product as "Product", 0 as "id_order"
FOR XML PATH('Order'), TYPE
)
END
go
exec insertOrder '<a/>', '<b/>'
select * from xOrder
Best regards
Michael
"luca" <luca@.discussions.microsoft.com> wrote in message
news:1F06F5E3-E9C9-4B40-ACFE-2784345D1F40@.microsoft.com...
> I'm searching for a way to insert nodes from an xml document into an
> other.
> My solution to the problem is something like this:
> ALTER PROCEDURE [dbo].[insertOrder]
> @.Customer xml (dbo.schemaCustomer),
> @.Product xml (dbo.schemaProduct)
> AS
> BEGIN
> declare @.app varchar(1024)
> declare @.x xml
> set @.app = '<Order><Product>' + CAST(@.Product as varchar(1024)) +
> '</Product>'+ CAST(@.Customer as varchar(1024)) +
> '<id_order>0</id_order></Order>'
> insert into xOrder (doc) values (@.app)
>
> END
> but I think there's a better way to solve the problem.
> Is it possible to do this witout any casting operation?
> Thank you, Luca.
>