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

Annoying problem with XQuery Expand / Collapse
Author
Message
Posted Thursday, January 6, 2011 3:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 12, 2013 5:59 AM
Points: 3, Visits: 41
Hi,

I have following xml document:

<Root>
<Header>
<SomeTag1></SomeTag1>
<SomeTag2></SomeTag2>
</Header>
<Data>
<Item ID="1" AdditionalInfo="Some info">
<SubItem ID="1">1</SubItem>
<SubItem ID="2">2</SubItem>
<SubItem ID="3">3</SubItem>
</Item>
<Item ID="2" AdditionalInfo="Some info">
<SubItem ID="1">1</SubItem>
<SubItem ID="2">2</SubItem>
</Item>
</Data>
</Root>

I need to extra each SubItem from the xml document into the following table:

Data (
SubItemId sometype,
SubItemValue sometype,
ItemId sometype,
AdditionalInfo somety),

where 'ItemId' and 'AdditionalInfo' are parent's values and 'SubItemValue' is text value of 'SubItem' tag.

How to solve this problem in the most efficient way ?

Thanks in advance,

Zoran
Post #1043591
Posted Thursday, January 6, 2011 4:04 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, November 13, 2014 9:47 AM
Points: 6,842, Visits: 13,368
Something like this (assuming you're using SS2K5 or above)?
DECLARE @xml XML
SELECT @xml='<Root>
<Data>
<Item ID="1" AdditionalInfo="Some info">
<SubItem ID="1">1</SubItem>
<SubItem ID="2">2</SubItem>
<SubItem ID="3">3</SubItem>
</Item>
<Item ID="2" AdditionalInfo="Some info">
<SubItem ID="1">1</SubItem>
<SubItem ID="2">2</SubItem>
</Item>
</Data>
</Root>'

SELECT
y.value('@ID[1]','INT') AS SubItemId,
y.value('.','INT') AS SubItemValue,
c.value('@ID[1]','INT') AS ItemId,
c.value('@AdditionalInfo[1]','varchar(30)') AS AdditionalInfo
FROM @xml.nodes('Root/Data/Item') T(c)
CROSS APPLY
c.nodes('SubItem') X(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 #1043618
Posted Thursday, January 6, 2011 4:48 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 12, 2013 5:59 AM
Points: 3, Visits: 41
Thanks for your post Lutz.

It solved my problem.

Bye.
Post #1043639
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse