Apply XSLT to an xml field in SQL Server 2005

Hi all,
Is it possible to apply an xslt template to an xml field in SQL Server 2005
by means of T-SQL? Or should I create a .NET assembly to perform this task?
Thanks in advance,
Alberto.Hello Alberto,

> Is it possible to apply an xslt template to an xml field in SQL Server
> 2005 by means of T-SQL? Or should I create a .NET assembly to perform
> this task?
Here you go: http://www.sqljunkies.com/WebLog/kt...l
t.aspx
Thanks,
Kent Tegels, DevelopMentor
http://staff.develop.com/ktegels/|||Thanks Kent.
Not only you answer my question, but also you give a good example.

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 trea
ting
@.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.
>

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.
>