XML Data Query Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

XML Data Query Help

Author

Topic

ljp099
Yak Posting Veteran

79 Posts

Posted - 2010-12-20 : 12:21:58

DECLARE @Data XML
DECLARE @Item nvarchar(4000)

SELECT @Data = content_html FROM content where folder_id = 30
and content_id = 6704

SELECT CONVERT(NVARCHAR(4000),
@Data.query('data(//SubjectArea)'))

Which selects the node SubjectArea nodes from xml data stored in a ntext column.

If a there are multiple subject area nodes, such as this:

<SubjectArea>Learning Measurement</SubjectArea>
<SubjectArea>LMS</SubjectArea>
<SubjectArea>Informal Learning</SubjectArea>

the result is returned as one long string. For example

Learning Measurement LMS Informal Learning

What I need returned is a delimited string like this:

Learning Measurement;LMS;Informal Learning;

How do I get the delimiter inserted in between the values?

Thanks for any help

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2010-12-20 : 14:25:48

Since the data is already stored in the table as XML, you could do something like this and avoid the intermediate steps:

select
	content_html.query('data(//SubjectArea)') ,
	';' as [text()]
from
	content
where
        folder_id = 30
for xml path('')

ljp099
Yak Posting Veteran

79 Posts

Posted - 2010-12-21 : 13:59:15

Im trying this, from your post, and getting the error:

Incorrect syntax near the keyword As

DECLARE @Data XML
DECLARE @Item nvarchar(4000)

SELECT @Data = content_html FROM content where folder_id = 30
and content_id = 6704

SELECT CONVERT(NVARCHAR(4000), @Data.query('data(//SubjectArea)') , ';' as [text()])

Sachin.Nand

2937 Posts

Posted - 2010-12-21 : 23:42:27

Are the no of nodes for SubjectArea be always 3 ?If that's the case then try this


declare @x xml='<root><SubjectArea>Learning Measurement</SubjectArea>
<SubjectArea>LMS</SubjectArea>
<SubjectArea>Informal Learning</SubjectArea></root>'



create table #t (x xml)
insert #t
select @x

select x.i.value('SubjectArea[1]' , 'varchar(100)') + ',' +
       x.i.value('SubjectArea[2]' , 'varchar(100)') + ',' +
       x.i.value('SubjectArea[3]' , 'varchar(100)')
       from #t t
cross apply t.x.nodes('/root')x(i)

drop table #t

PBUH

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2010-12-22 : 06:52:38

Hi,
Try this

declare @x xml='<root><SubjectArea>Learning Measurement</SubjectArea>
<SubjectArea>LMS</SubjectArea>
<SubjectArea>Informal Learning</SubjectArea></root>'

select t.i.value('.','varchar(100)') + ';'
from
@x.nodes('//SubjectArea') t(i)
for xml path('')

Iam a slow walker but i never walk back

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2010-12-22 : 06:57:25

quote:

Originally posted by Sachin.Nand

Are the no of nodes for SubjectArea be always 3 ?If that's the case then try this


declare @x xml='<root><SubjectArea>Learning Measurement</SubjectArea>
<SubjectArea>LMS</SubjectArea>
<SubjectArea>Informal Learning</SubjectArea></root>'



create table #t (x xml)
insert #t
select @x

select x.i.value('SubjectArea[1]' , 'varchar(100)') + ',' +
       x.i.value('SubjectArea[2]' , 'varchar(100)') + ',' +
       x.i.value('SubjectArea[3]' , 'varchar(100)')
       from #t t
cross apply t.x.nodes('/root')x(i)

drop table #t

PBUH

Hi sachin,
slight modification i have done to your code if you dont mind.
used xml path(' ') which is better solution for concatinating xml data rather than concating each and every subject data manually
Pls correct me if iam wrong

Hi sachin,
slight modification i have done to your code if you dont mind.
used xml path(' ') which is better solution for concatination xml data.

Iam a slow walker but i never walk back

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2010-12-22 : 06:58:10

if OBJECT_ID('tempdb..#t') is not null
begin
drop table #t
end

declare @x xml='<root><SubjectArea>Learning Measurement</SubjectArea>
<SubjectArea>LMS</SubjectArea>
<SubjectArea>Informal Learning</SubjectArea></root>'

create table #t (x xml)
insert #t
select @x

select t.i.value('.','varchar(100)') + ';'
from
#t
cross apply
x.nodes('//SubjectArea') t(i)
for xml path('')

Iam a slow walker but i never walk back

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2010-12-22 : 12:26:19

May be all you are looking for is the following. It is quick and dirty (special emphasis on the "dirty"). This should work even if you have more than 3 subject areas in your html data.

quote:
Originally posted by ljp099

DECLARE @Data XML
DECLARE @Item nvarchar(4000)

SELECT @Data = content_html FROM content where folder_id = 30
and content_id = 6704

SELECT replace(replace(CONVERT(NVARCHAR(4000),
@Data.query('~~data(~~//SubjectArea)')),'<SubjectArea>',''),'</SubjectArea>',';')

Which selects the node SubjectArea nodes from xml data stored in a ntext column.

If a there are multiple subject area nodes, such as this:

<SubjectArea>Learning Measurement</SubjectArea>
<SubjectArea>LMS</SubjectArea>
<SubjectArea>Informal Learning</SubjectArea>

the result is returned as one long string. For example

Learning Measurement LMS Informal Learning

What I need returned is a delimited string like this:

Learning Measurement;LMS;Informal Learning;

How do I get the delimiter inserted in between the values?

Thanks for any help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources