February 10, 2012 at 12:57 pm
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
February 10, 2012 at 2:09 pm
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 *******
February 10, 2012 at 3:49 pm
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?
February 10, 2012 at 5:56 pm
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
February 10, 2012 at 6:15 pm
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?
February 11, 2012 at 8:59 am
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/61537February 11, 2012 at 9:30 am
Thank you soo much Matt!!!:-)
February 11, 2012 at 10:51 am
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