将SQL中的XML字符串解析为特定列

My XML file looks like this:

我的XML文件如下所示:

<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:

使用SQL,我想将我的数据放入这些特定的列:

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

CIM | Messgae |分类名称| InstanceID | PropertyName |价值| DisplayValue

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

我很抱歉,因为这个问题非常类似于这里发布的问题:使用SQL将XML文件解析为特定列

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

但是我被用户批评再次发布问题,因为我稍微改变了我的XML文档。

Diagram to help demonstrate XML schema:

帮助演示XML模式的图表:

1 个解决方案

#1

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>.

某些值在xml中只有一次。我直接从你的@xml中获取它们。作为第二级,您有重复元素和 - embedded - 重复元素。

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 |
+-------------+------------+---------------------+---------------------+-------------------------+---------------------------+---------------------+

#1