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

Reading match pair of descandats of XML elements in xquiry through T-Sql Expand / Collapse
Author
Message
Posted Sunday, March 9, 2014 11:25 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, September 28, 2014 5:21 AM
Points: 2, Visits: 155
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
Post #1549064
Posted Monday, March 10, 2014 7:44 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 8:55 AM
Points: 2,873, Visits: 5,185
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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1549228
Posted Sunday, March 16, 2014 3:09 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, September 28, 2014 5:21 AM
Points: 2, Visits: 155
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.
Post #1551596
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse