Parse XML string in SQL into Specific Columns

Question

My XML file looks like this:

<CIM CIMVERSION="2.0" DTDVERSION="2.0">
  <MESSAGE ID="4711" PROTOCOLVERSION="1.0">
    <SIMPLEREQ>
      <VALUE.NAMEDINSTANCE>
        <INSTANCENAME CLASSNAME="DCIM_ControllerView">
          <KEYBINDING NAME="InstanceID">
            <KEYVALUE VALUETYPE="string">RAID.Integrated.1-1</KEYVALUE>
          </KEYBINDING>
        </INSTANCENAME>
        <INSTANCE CLASSNAME="DCIM_ControllerView">
          <PROPERTY NAME="LastUpdateTime" TYPE="string">
            <VALUE>20170223172914.000000+000</VALUE>
            <DisplayValue>2017-02-23T17:29:14</DisplayValue>
          </PROPERTY>
          <PROPERTY NAME="LastSystemInventoryTime" TYPE="string">
            <VALUE>20170223172914.000000+000</VALUE>
            <DisplayValue>2017-02-23T17:29:14</DisplayValue>
          </PROPERTY>
          <PROPERTY NAME="RealtimeCapability" TYPE="uint32">
            <VALUE>1</VALUE>
            <DisplayValue>Capable</DisplayValue>
          </PROPERTY>
        </INSTANCE>
      </VALUE.NAMEDINSTANCE>
      <VALUE.NAMEDINSTANCE>
        <INSTANCENAME CLASSNAME="DCIM_ControllerView">
          <KEYBINDING NAME="InstanceID">
            <KEYVALUE VALUETYPE="string">AHCI.Embedded.1-1</KEYVALUE>
          </KEYBINDING>
        </INSTANCENAME>
        <INSTANCE CLASSNAME="DCIM_ControllerView">
          <PROPERTY NAME="LastUpdateTime" TYPE="string">
            <VALUE>20170106154535.000000+000</VALUE>
            <DisplayValue>2017-01-06T15:45:35</DisplayValue>
          </PROPERTY>
          <PROPERTY NAME="LastSystemInventoryTime" TYPE="string">
            <VALUE>20170223170150.000000+000</VALUE>
            <DisplayValue>2017-02-23T17:01:50</DisplayValue>
          </PROPERTY>
          <PROPERTY NAME="RealtimeCapability" TYPE="uint32">
            <VALUE>0</VALUE>
            <DisplayValue>Incapable</DisplayValue>
          </PROPERTY>         
        </INSTANCE>
      </VALUE.NAMEDINSTANCE>
      <VALUE.NAMEDINSTANCE>
        <INSTANCENAME CLASSNAME="DCIM_ControllerView">
          <KEYBINDING NAME="InstanceID">
            <KEYVALUE VALUETYPE="string">AHCI.Embedded.2-1</KEYVALUE>
          </KEYBINDING>
        </INSTANCENAME>
        <INSTANCE CLASSNAME="DCIM_ControllerView">
          <PROPERTY NAME="LastUpdateTime" TYPE="string">
            <VALUE>20170106154535.000000+000</VALUE>
            <DisplayValue>2017-01-06T15:45:35</DisplayValue>
          </PROPERTY>
          <PROPERTY NAME="LastSystemInventoryTime" TYPE="string">
            <VALUE>20170223170150.000000+000</VALUE>
            <DisplayValue>2017-02-23T17:01:50</DisplayValue>
          </PROPERTY>
          <PROPERTY NAME="RealtimeCapability" TYPE="uint32">
            <VALUE>0</VALUE>
            <DisplayValue>Incapable</DisplayValue>
          </PROPERTY>         
        </INSTANCE>
      </VALUE.NAMEDINSTANCE>
      <VALUE.NAMEDINSTANCE>
        <INSTANCENAME CLASSNAME="DCIM_MemoryView">
          <KEYBINDING NAME="InstanceID">
            <KEYVALUE VALUETYPE="string">DIMM.Socket.A1</KEYVALUE>
          </KEYBINDING>
        </INSTANCENAME>
        <INSTANCE CLASSNAME="DCIM_MemoryView">
          <PROPERTY NAME="LastUpdateTime" TYPE="string">
            <VALUE>20170106144535.000000+000</VALUE>
            <DisplayValue>2017-01-06T14:45:35</DisplayValue>
          </PROPERTY>        
        </INSTANCE>
      </VALUE.NAMEDINSTANCE>
    </SIMPLEREQ>
  </MESSAGE>
</CIM>

Using SQL, I want to get my data into these specific columns:

CIM | Messgae | Classname | InstanceID | PropertyName | Value | DisplayValue

I apologize since this question is VERY similar to the one posted here: Parse XML file using SQL into Specific Columns

But I was criticized by users to post the question again because I had changed my XML document slightly.

Diagram to help demonstrate XML schema:

About because I had changed my XML document slightly. You did not provide the xml you provide here, but a different one toegther with a schema. And it is no tiny change when 1:1 turns to 1:n... Happy coding! — Gottfried Lesigang
– Gottfried Lesigang, Commented Mar 6, 2018 at 15:53

Gottfried Lesigang · Accepted Answer · 2018-03-06 15:51:53Z

Try it like this:

SELECT @xml.value(N'(/CIM/@CIMVERSION)[1]',N'nvarchar(max)') AS CIM_version
      ,@xml.value(N'(/CIM/MESSAGE/@ID)[1]',N'nvarchar(max)') AS Message_Id

      ,vni.value(N'(INSTANCENAME/KEYBINDING/KEYVALUE/text())[1]',N'nvarchar(max)') AS Keybinding_Value

      ,vni.value(N'(INSTANCE/@CLASSNAME)[1]',N'nvarchar(max)') AS Instance_ClassName

      ,prp.value(N'@NAME',N'nvarchar(max)') AS Prop_Name
      ,prp.value(N'(VALUE/text())[1]',N'nvarchar(max)') AS Prop_Value
      ,prp.value(N'(DisplayValue/text())[1]',N'nvarchar(max)') AS Prop_DisplayValue
FROM @xml.nodes(N'/CIM/MESSAGE/SIMPLEREQ/VALUE.NAMEDINSTANCE') AS A(vni)
CROSS APPLY vni.nodes('INSTANCE/PROPERTY') AS B(prp);

Some values are only once within your xml. I fetch them directly from your @xml. As the second level you have repeating elements <VALUE.NAMEDINSTANCE> and - embedded - repeating elements <PROPERTY>.

The result

+-------------+------------+---------------------+---------------------+-------------------------+---------------------------+---------------------+
| CIM_version | Message_Id | Keybinding_Value    | Instance_ClassName  | Prop_Name               | Prop_Value                | Prop_DisplayValue   |
+-------------+------------+---------------------+---------------------+-------------------------+---------------------------+---------------------+
| 2.0         | 4711       | RAID.Integrated.1-1 | DCIM_ControllerView | LastUpdateTime          | 20170223172914.000000+000 | 2017-02-23T17:29:14 |
+-------------+------------+---------------------+---------------------+-------------------------+---------------------------+---------------------+
| 2.0         | 4711       | RAID.Integrated.1-1 | DCIM_ControllerView | LastSystemInventoryTime | 20170223172914.000000+000 | 2017-02-23T17:29:14 |
+-------------+------------+---------------------+---------------------+-------------------------+---------------------------+---------------------+
| 2.0         | 4711       | RAID.Integrated.1-1 | DCIM_ControllerView | RealtimeCapability      | 1                         | Capable             |
+-------------+------------+---------------------+---------------------+-------------------------+---------------------------+---------------------+
| 2.0         | 4711       | AHCI.Embedded.1-1   | DCIM_ControllerView | LastUpdateTime          | 20170106154535.000000+000 | 2017-01-06T15:45:35 |
+-------------+------------+---------------------+---------------------+-------------------------+---------------------------+---------------------+
| 2.0         | 4711       | AHCI.Embedded.1-1   | DCIM_ControllerView | LastSystemInventoryTime | 20170223170150.000000+000 | 2017-02-23T17:01:50 |
+-------------+------------+---------------------+---------------------+-------------------------+---------------------------+---------------------+
| 2.0         | 4711       | AHCI.Embedded.1-1   | DCIM_ControllerView | RealtimeCapability      | 0                         | Incapable           |
+-------------+------------+---------------------+---------------------+-------------------------+---------------------------+---------------------+
| 2.0         | 4711       | AHCI.Embedded.2-1   | DCIM_ControllerView | LastUpdateTime          | 20170106154535.000000+000 | 2017-01-06T15:45:35 |
+-------------+------------+---------------------+---------------------+-------------------------+---------------------------+---------------------+
| 2.0         | 4711       | AHCI.Embedded.2-1   | DCIM_ControllerView | LastSystemInventoryTime | 20170223170150.000000+000 | 2017-02-23T17:01:50 |
+-------------+------------+---------------------+---------------------+-------------------------+---------------------------+---------------------+
| 2.0         | 4711       | AHCI.Embedded.2-1   | DCIM_ControllerView | RealtimeCapability      | 0                         | Incapable           |
+-------------+------------+---------------------+---------------------+-------------------------+---------------------------+---------------------+
| 2.0         | 4711       | DIMM.Socket.A1      | DCIM_MemoryView     | LastUpdateTime          | 20170106144535.000000+000 | 2017-01-06T14:45:35 |
+-------------+------------+---------------------+---------------------+-------------------------+---------------------------+---------------------+

@AlanPear Great, glad to help you. At the moment Columbo has no questions left, someone has to go to jail :-D

Collectives™ on Stack Overflow

Parse XML string in SQL into Specific Columns

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related