Annoying problem with XQuery

• zoranrdc

  SSC Veteran

  Points: 211

  More actions

  January 6, 2011 at 3:14 am

  #232734

  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

• LutzM

  SSC Guru

  Points: 107049

  More actions

  January 6, 2011 at 4:04 am

  #1269976

  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[/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]

• zoranrdc

  SSC Veteran

  Points: 211

  More actions

  January 6, 2011 at 4:48 am

  #1269994

  Thanks for your post Lutz.

  It solved my problem.

  Bye.:-)