xml query

Question

Post reply

xml query

cbabino71

SSC Journeyman

Points: 75
More actions
February 10, 2012 at 12:57 pm

#391533

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

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

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

SGT_squeequal SSCertifiable Points: 7167 More actions · Answer 1

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://qa.sqlservercentral.com/Forums/Topic1243118-338-1.aspx#bm1243478

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 2

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?

cbabino71 SSC Journeyman Points: 75 More actions · Answer 3

HI Matt

Thanks a lot for your great response.

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

<PHONE>

</PHONE>

<PHONE>

</PHONE>

</PHONES>

thanks

Conan

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

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?

Mark Cowne One Orange Chip Points: 26865 More actions · Answer 5

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