Parsing Non-Standard XML in TSQL

Question

I need help parsing this XML in TSQL:

<Settings>
    <Setting ERName="CAPTURE_MODE_ID" Value="9" />
    <Setting ERName="VEHICLE_TYPE" Value="7" />
</Settings>

I would like to return the values as such:

Capture_Mode_Id        Vehicle_Type
9                      7

What is non-standard about the XML in your question?

J0e3gan
– J0e3gan

2014-01-22 03:29:24 +00:00
Commented Jan 22, 2014 at 3:29 — J0e3gan
– J0e3gan, Commented Jan 22, 2014 at 3:29

Mikael Eriksson · Accepted Answer · 2014-01-22 06:42:14Z

1

SQL Fiddle

declare @XML xml

set @XML = '
<Settings>
 <Setting ERName="CAPTURE_MODE_ID" Value="9" />
 <Setting ERName="VEHICLE_TYPE" Value="7" />
</Settings>'

select @XML.value('(/Settings/Setting[@ERName = "CAPTURE_MODE_ID"]/@Value)[1]', 'int') as CAPTURE_MODE_ID,
       @XML.value('(/Settings/Setting[@ERName = "VEHICLE_TYPE"]/@Value)[1]', 'int') as VEHICLE_TYPE

Results:

| CAPTURE_MODE_ID | VEHICLE_TYPE |
|-----------------|--------------|
|               9 |            7 |

answered Jan 22, 2014 at 6:42

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

J0e3gan · Accepted Answer · 2014-01-22 05:19:16Z

You need to perform two logical steps as best I can tell:

Parse the XML - i.e. get a result set from it.
Pivot the result set.

To get a result set from the XML, use the OPENXML function - something like the following...

DECLARE @idoc int, @doc varchar(1000);

-- the XML document
SET @doc ='
<ROOT>
<Settings>
    <Setting ERName="CAPTURE_MODE_ID" Value="9" />
    <Setting ERName="VEHICLE_TYPE" Value="7" />
</Settings>
</ROOT>';

-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc;

-- Execute a SELECT statement that uses the OPENXML rowset provider.
SELECT *
FROM OPENXML (@idoc, '/ROOT/Settings/Setting', 1)
    WITH (ERName varchar(20), Value varchar(20));

..., which yields the following results:

ERName                 Value
---------------        -----
CAPTURE_MODE_ID        9
VEHICLE_TYPE           7

To pivot the result set without aggregation, consider the ideas presented to this end in a CALSQL blog post.

Collectives™ on Stack Overflow

Parsing Non-Standard XML in TSQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related