RE: xml nodes and user defined function

Please try the following:

create table test(id int identity(1,1), doc varchar(max))

create view dbo.[doc]
as
select id,
col.value('(./last_name)[1]','varchar(200)') as [Last Name],
col.value('(./first_name)[1]','varchar(200)') as [First Name]
from (select id, convert(xml, doc) as doc from test) u
cross apply doc.nodes('people/person') as t(col)

Regards,
Galex Yen

"ycgq@xxxxxxxxx" wrote:

Here is the script:

create table test(id int identity(1,1), doc varchar(max))
create view dbo.[doc]
as
select id,
col.value('(./last_name)[1]','varchar(200)') as [Last Name],
col.value('(./first_name)[1]','varchar(200)') as [First Name]
from [test] u
cross apply (cast (doc as xml)).nodes('people/person') as t(col)

Doc is in xml format. There is error:Incorrect syntax near the keyword
'as'.

So I changed to use UDF.
create function dbo.toXML(@input nvarchar(max))
returns XML
as
begin
declare @xml_data xml
set @xml_data = cast(@input as xml)
return @xml_data
end

create view dbo.[doc]
as
select id,
col.value('(./last_name)[1]','varchar(200)') as [Last Name],
col.value('(./first_name)[1]','varchar(200)') as [First Name]
from [test] u
cross apply (toXML(doc)).nodes('people/person') as t(col)

But it still complains about "Incorrect syntax near ')'". Anyone can
help? Thanks a lot!


.

Relevant Pages
Quantcast