Reading match pair of descandats of XML elements in xquiry through T-Sql

• danyc

  Valued Member

  Points: 55

  More actions

  March 9, 2014 at 11:26 am

  #148519

  In the following XML

  <Recordset xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

  <Products>

  <Productdetails>

  <Productkey>520000522</Productkey>

  </Productdetails>

  <Productdetails>

  <Productkey>816700333</Productkey>

  </Productdetails>

  <Productdetails>

  <Productkey>520444422</Productkey>

  </Productdetails>

  <Productcategory>

  <Productcat>abc</Productcat>

  </Productcategory>

  </Products>

  <Products>

  <Productdetails>

  <Productkey>53330511</Productkey>

  </Productdetails>

  <Productcategory>

  <Productcat>def</Productcat>

  </Productcategory>

  </Prtoducts>

  <Products>

  <Productdetails>

  <Productkey>511144122</Productkey>

  </Productdetails>

  <Productdetails>

  <Productkey>511765122</Productkey>

  </Productdetails>

  <Productcategory>

  <Productcat>fgh</Productcat>

  </Productcategory>

  </Prtoducts>

  <Products>

  <Productdetails>

  <Productkey>58800512</Productkey>

  </Productdetails>

  <Productcategory>

  <Productcat>ter</Productcat>

  </Productcategory>

  </Products>

  </Recordset>

  , I wrote the following statement to get Productkey of Productdetails in sequence of the <Products>.

  declare @XML xml

  set @XML = -- the above XML

  SELECT

  doc.col.value('Productkey[1]', 'int')

  FROM @xml.nodes('Recordset/Products/Productdetails/Productkey/')

  doc(col)

  SELECT

  doc.col.value('Productcat[1]', 'nvarchar(10)')

  FROM @xml.nodes('Recordset/Products/Productcategory/Productcat/')

  doc(col)

  I got the <productkey> and the <productcat> separately in different column tables.

  But how I can a match pair of the proper <productkey> and <productcat> in two col in the same table or how I can read matched pair col at once ?

  For example pair values (520000522,'abc'), (816700333,'abc'),(520444422,'abc'),(53330511,'def') etc

  Thank in advanced

  For reply

• Eugene Elutin

  SSC Guru

  Points: 59322

  More actions

  March 10, 2014 at 7:44 am

  #1695521

  declare @XML xml

  set @XML =

  '<Recordset xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

  <Products>

  <Productdetails>

  <Productkey>520000522</Productkey>

  </Productdetails>

  <Productdetails>

  <Productkey>816700333</Productkey>

  </Productdetails>

  <Productdetails>

  <Productkey>520444422</Productkey>

  </Productdetails>

  <Productcategory>

  <Productcat>abc</Productcat>

  </Productcategory>

  </Products>

  <Products>

  <Productdetails>

  <Productkey>53330511</Productkey>

  </Productdetails>

  <Productcategory>

  <Productcat>def</Productcat>

  </Productcategory>

  </Products>

  <Products>

  <Productdetails>

  <Productkey>511144122</Productkey>

  </Productdetails>

  <Productdetails>

  <Productkey>511765122</Productkey>

  </Productdetails>

  <Productcategory>

  <Productcat>fgh</Productcat>

  </Productcategory>

  </Products>

  <Products>

  <Productdetails>

  <Productkey>58800512</Productkey>

  </Productdetails>

  <Productcategory>

  <Productcat>ter</Productcat>

  </Productcategory>

  </Products>

  </Recordset>'

  SELECT doc.col.value('Productkey[1]', 'int'),doc.col.value('../Productcategory[1]/Productcat[1]', 'varchar(10)')

  FROM @xml.nodes('Recordset/Products/Productdetails') doc(col)

  _____________________________________________
  "The only true wisdom is in knowing you know nothing"
  "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
  (So many miracle inventions provided by MS to us...)

  How to post your question to get the best and quick help[/url]

• danyc

  Valued Member

  Points: 55

  More actions

  March 16, 2014 at 3:09 pm

  #1697511

  Thank

  Eugene Elutin for editing my XML.

  I resolved my problem. it was a matter of decompund of third or fourth xml hirarchy level by nodes.query method and then i got the solution.:-)