Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Querying XML data Expand / Collapse
Author
Message
Posted Saturday, May 3, 2014 4:21 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 19, 2014 12:12 AM
Points: 43, Visits: 42
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
------------
notedate notetext
2014-03-01 Here is a sample text 1
2014-03-02 Here is a sample text 2
2014-03-03 Here is a sampe text 3
2014-03-04 Here 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
Post #1567294
Posted Saturday, May 3, 2014 5:32 PM This worked for the OP Answer marked as solution


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:40 AM
Points: 6,842, Visits: 13,372
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
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1567303
Posted Sunday, May 4, 2014 3:01 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 19, 2014 12:12 AM
Points: 43, Visits: 42
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)

Post #1567321
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse