1

I have following XML :

<PW_main_Root_>
    <Trigger_data_new_ID>30</Trigger_data_new_ID>
    <Trigger_Event>
        <OperatorID>30</OperatorID>
        <MainData>516274981</MainData>
        <Variables>
            <Variable VarID="8040" Value="00000000"/>
            <Variable VarID="8140" Value="50000000"/>
            <Variable VarID="8240" Value="00000000"/>
            <Variable VarID="8340" Value="00000000"/>
            <Variable VarID="8440" Value="00000000"/>
            <Variable VarID="8540" Value="00000000"/>
            <Variable VarID="8640" Value="00080000"/>
            <Variable VarID="8740" Value="01000000"/>
            <Variable VarID="8840" Value="000E0000"/>
            <Variable VarID="8940" Value="00000000"/>
            <Variable VarID="8B40" Value="0F000000"/>
            <Variable VarID="8A40" Value="00000000"/>
            <Variable VarID="0F40" Value="00000000"/>
        </Variables>
    </Trigger_Event>
</PW_main_Root_>

Now I want to parse this XML in sql server and want it like:

VarID    Value
-------------------
8040    00000000
8140    50000000
8240    00000000
...and so on

How can I do this ?

I have SQL Server 2008, so none of the functions of SQL Server 2014 will work here.

Improve this question

1 Answer 1

Reset to default
1

Example

Select VarID = x.v.value('@VarID','VARCHAR(50)')
      ,Value = x.v.value('@Value','VARCHAR(50)')
 From  @Xml.nodes('PW_main_Root_/Trigger_Event/Variables/Variable') x(v)

Returns

VarID   Value
8040    00000000
8140    50000000
8240    00000000
8340    00000000
8440    00000000
8540    00000000
8640    00080000
8740    01000000
8840    000E0000
8940    00000000
8B40    0F000000
8A40    00000000
0F40    00000000
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.