Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

Stuck parsing xml data SQL2005

Options
  • 15-08-2012 10:07am
    #1
    TinCool
    Registered Users 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