Querying XML data

Question

Post reply

Querying XML data

mviggers

Old Hand

Points: 339
More actions
May 3, 2014 at 4:21 pm

#292947

Hi
I hope someone is able to help me with following query:
--LOAD TEST XML TO SQL SERVER XML COLUMN
declare @xml table (xmldata xml)
insert @xml select
'<organization>
<department>
<person>
<personId>1</personId>
<name>John Doe</name>
<note>
<date>2014-03-01</date>
<text>Here is a sample text 1</text>
</note>
<note>
<date>2014-03-02</date>
<text>Here is a sample text 2</text>
</note>
</person>
<person>
<personId>2</personId>
<name>Jane Doe</name>
<note>
<date>2014-03-03</date>
<text>Here is a sampe text 3</text>
</note>
<note>
<date>2014-03-04</date>
<text>Here is a sampe text 4</text>
</note>
</person>
</department>
</organization>'
--SELECT PERSON OBJECTS TO TABLE
select person.x.value('(./personId)[1]','varchar(max)') as personId, person.x.value('(./name)[1]','varchar(max)') as name
from @xml e
cross apply e.xmldata.nodes('/organization/department/person') as person(x)
/*
Result
------------
personid name
1 John Doe
2 Jane Doe
*/
--SELECT NOTE OBJECTS TO TABLE
select note.x.value('(./date)[1]','varchar(max)') as notedate, note.x.value('(./text)[1]','varchar(max)') as notetext
from @xml e
cross apply e.xmldata.nodes('/organization/department/person/note') as note(x)
/*
Result
------------
notedatenotetext
2014-03-01Here is a sample text 1
2014-03-02Here is a sample text 2
2014-03-03Here is a sampe text 3
2014-03-04Here is a sampe text 4
*/
All above is correct. However I need the personId from person object to be selected in the note result (to use as foreign key). Expected result from note query is:
/*
Result
------------
personId notedate notetext
1 2014-03-01 Here is a sample text 1
1 2014-03-02 Here is a sample text 2
2 2014-03-03 Here is a sampe text 3
2 2014-03-04 Here is a sampe text 4
*/
Any advice is appreciated 🙂
/Mikkel

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

something like tihs?

select

person.x.value('(./personId)[1]','varchar(max)') as personId,

person.x.value('(./name)[1]','varchar(max)') as name,

note.y.value('(./date)[1]','varchar(max)') as [date],

note.y.value('(./text)[1]','varchar(max)') as [text]

from @xml e

cross apply e.xmldata.nodes('organization/department/person') as person(x)

outer apply person.x.nodes('note') as note(y)

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

mviggers Old Hand Points: 339 More actions · Answer 2

LutzM: Thank you, works as expected. Appreciate your help :-)...

LutzM (5/3/2014)
something like tihs?
select
person.x.value('(./personId)[1]','varchar(max)') as personId,
person.x.value('(./name)[1]','varchar(max)') as name,
note.y.value('(./date)[1]','varchar(max)') as [date],
note.y.value('(./text)[1]','varchar(max)') as [text]
from @xml e
cross apply e.xmldata.nodes('organization/department/person') as person(x)
outer apply person.x.nodes('note') as note(y)