xml query

  • DECLARE @xmlvar XML = '<RECORDS>

    <RECORD>

    <CUSTOMERINFO>

    <ID>1</ID>

    <NAME>MIKE BROWN</NAME>

    </CUSTOMERINFO>

    <ADDRESS>

    <STREET> 1 MAIN ST </STREET>

    <CITY>BOSTON </CITY>

    <STATE>MA</STATE>

    <ZIP>01111</ZIP>

    <PHONES>

    <TYPE>CELL</TYPE>

    <NUMBER>111-1111</NUMBER>

    <TYPE>HOME</TYPE>

    <NUMBER>112-1111</NUMBER>

    </PHONES>

    </ADDRESS>

    </RECORD>

    <RECORD>

    <CUSTOMERINFO>

    <ID>2</ID>

    <NAME>JOE BROWN</NAME>

    </CUSTOMERINFO>

    <ADDRESS>

    <STREET> 25 RIVER ROAD</STREET>

    <CITY>NEWPORT </CITY>

    <STATE>RI</STATE>

    <ZIP>45111</ZIP>

    <PHONES>

    <TYPE>CELL</TYPE>

    <NUMBER>222-2222</NUMBER>

    <TYPE>HOME</TYPE>

    <NUMBER>221-2222</NUMBER>

    </PHONES>

    </ADDRESS>

    </RECORD>

    </RECORDS>'

    select

    col.value('(ADDRESS/PHONES/TYPE)[1]', 'VARCHAR(40)' ) AS TYPEOFPHONE,

    col.value('(ADDRESS/PHONES/NUMBER)[1]', 'VARCHAR(40)' ) AS NUMBER

    FROM @xMLVAR.nodes('/RECORDS/RECORD') AS Feed(Col)

    where col.value('(CUSTOMERINFO/ID)[1]', 'tinyint') = 1

    If you run this query, you will notice it only returns the first row for the phones for the first customer . How do I get the 2 records of TYPEOFPHONE and NUMBER for the first customer : MIKE BROWN (ID =1).

    thanks in advance for your help

  • Lo not sure if there is an easier way but, i had a large XML file that had multiple sections of the same data, same idea as the XML you posted in, i created a script to parse throught the XML to find the first occurance of my data and populate that into a table, then find the next section of data and so on. then query that table to retrieve the collumns for all the occurances.

    have a look at http://www.sqlservercentral.com/Forums/Topic1243118-338-1.aspx#bm1243478

    ***The first step is always the hardest *******

  • The issue is that you're repeating sequences of nodes (i.e NUMBER and TYPE) without encapsulating them, so the querying is reasonably ugly. If you were to add the PHONE level, your querying becomes a LOT easier.

    As in:

    <PHONES>

    <PHONE>

    <TYPE>CELL</TYPE>

    <NUMBER>222-2222</NUMBER>

    </PHONE>

    <PHONE>

    <TYPE>HOME</TYPE>

    <NUMBER>221-2222</NUMBER>

    </PHONE>

    </PHONES>

    Here's the cleanest query I can come up. This query essentially rebuilds the missing level on the fly (so it probably will start cranking if you have large XML files).

    select

    p.value('(number)[1]', 'VARCHAR(40)' ),

    p.value('(type)[1]', 'VARCHAR(40)' )

    FROM @xMLVAR.nodes('/RECORDS/RECORD') AS Feed(Col)

    cross apply col.nodes('ADDRESS/PHONES') as Contact(phone)

    cross apply (

    SELECT phone.query(' for $s in (1,2,3,4,5,6,7,8,9,10)

    return <phone>

    <number>{NUMBER[$s]}</number>

    <type>{TYPE[$s]}</type>

    </phone>') as nbr

    ) a

    cross apply a.nbr.nodes('phone') indp(p)

    where

    col.value('(CUSTOMERINFO/ID)[1]', 'tinyint') = 1 and

    p.value('(type)[1]', 'VARCHAR(40)' )<>''

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • HI Matt

    Thanks a lot for your great response.

    JUst curious how much easier would it be if it was like you had suggested:

    <PHONES>

    <PHONE>

    <TYPE>CELL</TYPE>

    <NUMBER>111-1111</NUMBER>

    </PHONE>

    <PHONE>

    <TYPE>HOME</TYPE>

    <NUMBER>112-1111</NUMBER>

    </PHONE>

    </PHONES>

    thanks

    Conan

  • Here's about as simple as it gets:

    select

    phone.value('(NUMBER)[1]', 'VARCHAR(40)' ),

    phone.value('(TYPE)[1]', 'VARCHAR(40)' )

    FROM @xMLVAR.nodes('/RECORDS/RECORD[(./CUSTOMERINFO/ID)[1]="1"]/ADDRESS/PHONES/PHONE') as Contact(phone)

    If you prefer to keep your criteria out of the nodes function (I actually think this one reads a little better):

    select

    phone.value('(NUMBER)[1]', 'VARCHAR(40)' ),

    phone.value('(TYPE)[1]', 'VARCHAR(40)' )

    FROM @xMLVAR.nodes('/RECORDS/RECORD') AS Feed(Col)

    cross apply col.nodes('ADDRESS/PHONES/PHONE') as Contact(phone)

    where

    col.value('(CUSTOMERINFO/ID)[1]', 'tinyint') = 1

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • Here's another way using a hideous xquery

    select

    P.N.value('.', 'VARCHAR(40)' ) AS TYPEOFPHONE,

    P.N.value('for $s in . return ../NUMBER[count(../TYPE[. << $s]) + 1][1]', 'VARCHAR(40)') AS NUMBER

    FROM @xMLVAR.nodes('/RECORDS/RECORD') AS Feed(Col)

    CROSS APPLY Feed.Col.nodes('ADDRESS/PHONES/TYPE') AS P(N)

    where col.value('(CUSTOMERINFO/ID)[1]', 'tinyint') = 1

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537
  • Thank you soo much Matt!!!:-)

  • Thanks a lot Mark for your very helpful reponse.:-)

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply