Advertisement
Help Keep Boards Alive. Support us by going ad free today. See here: https://subscriptions.boards.ie/.
If we do not hit our goal we will be forced to close the site.

Current status: https://keepboardsalive.com/

Annual subs are best for most impact. If you are still undecided on going Ad Free - you can also donate using the Paypal Donate option. All contribution helps. Thank you.
https://www.boards.ie/group/1878-subscribers-forum

Private Group for paid up members of Boards.ie. Join the club.

Stuck parsing xml data SQL2005

  • 15-08-2012 10:07AM
    #1
    Registered Users, Registered Users 2 Posts: 2,894 ✭✭✭


    Hey guys.

    I have the below xml:
    DECLARE	@xml	AS XML
    SET	@xml = CAST(CAST('<?xml version="1.0" encoding="utf-16"?>
    <error application="ABCD" host="ABCD">
    	<additionalInfo>
    		<item name="1"><value string="A" /></item>
    		<item name="2"><value string="B" /></item>
    		<item name="3"><value string="C" /></item>
    		<item name="4"><value string="D" /></item>
    	</additionalInfo>
    </error>' AS NVARCHAR(MAX)) AS XML)
    
    SELECT 
    		[name] = itemname.value('(@name)', 'VARCHAR(250)')
    ,		[string] = itemvalue.value('(@string)','VARCHAR(250)')
    FROM	
    		@xml.nodes('//error/additionalInfo/item') AS T1(itemname)
    CROSS APPLY
    		@xml.nodes('//error/additionalInfo/item/value') AS T2(itemvalue)
    

    I want to write a query that returns the below:
    name	string
    1	A
    2	B
    3	C
    4	D
    

    I can get the data out but the results I'm getting is one entry for each combination of name and string. Is there any way I can join the item and value elements together in one simple select query ?

    To answer some questions I may get.

    1. I cannot change the structure of the source xml as this is what I am working with.
    2. I would rather not have to go down the road of creating temporary tables and IDENTITY columns, doing two selects and joining the two together
    3. I am aware I am double CASTING but that is due to the source xml data being utf-16, but it makes no difference to the structure of what I need to do.

    Cheers,
    TC


Advertisement