Home Forums SQL Server 2005 T-SQL (SS2K5) Reading match pair of descandats of XML elements in xquiry through T-Sql RE: Reading match pair of descandats of XML elements in xquiry through T-Sql

  • 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]