使用SQL将XML文件解析为特定列 [英] Parse XML file using SQL into Specific Columns

查看：77
本文介绍了使用SQL将XML文件解析为特定列的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我有一个看起来像这样的XML文件:

I have an XML file that looks like this:

<?xml version="1.0" encoding = "utf-8"?>
<CIM CIMVERSION="2.0" DTDVERSION="2.0">
<MESSAGE ID="1111" 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>6</VALUE>
         <DisplayValue>Capable</DisplayValue>
       </PROPERTY>
       <PROPERTY NAME="SupportControllerBootMode" TYPE="uint8">
         <VALUE>1</VALUE>
         <DisplayValue>Supported</DisplayValue>
       </PROPERTY>
       <PROPERTY NAME="SupportEnhancedAutoForeignImport" TYPE="uint8">
         <VALUE>1</VALUE>
         <DisplayValue>Supported</DisplayValue>
       </PROPERTY>
       <PROPERTY NAME="MaxAvailablePCILinkSpeed" TYPE="string">
         <VALUE>Generation 3</VALUE>
         <DisplayValue>Generation 3</DisplayValue>
       </PROPERTY>
       <PROPERTY NAME="MaxPossiblePCILinkSpeed" TYPE="string">
         <VALUE>Generation 3</VALUE>
         <DisplayValue>Generation 3</DisplayValue>
       </PROPERTY>
       <PROPERTY NAME="PatrolReadState" TYPE="uint8">
         <VALUE>1</VALUE>
         <DisplayValue>Stopped</DisplayValue>
       </PROPERTY>
       <PROPERTY NAME="DriverVersion" TYPE="string">
          <DisplayValue>Not Applicable</DisplayValue>
       </PROPERTY>
       <PROPERTY NAME="CacheSizeInMB" TYPE="uint32">
         <VALUE>0</VALUE>
         <DisplayValue>0 MB</DisplayValue>
       </PROPERTY>
       <PROPERTY NAME="SupportRAID10UnevenSpans" TYPE="uint8">
         <VALUE>1</VALUE>
         <DisplayValue>Supported</DisplayValue>
       </PROPERTY>
       <PROPERTY NAME="T10PICapability" TYPE="uint8">
         <VALUE>1</VALUE>
         <DisplayValue>Supported</DisplayValue>
       </PROPERTY>
       <PROPERTY NAME="SlicedVDCapability" TYPE="uint8">
         <VALUE>1</VALUE>
         <DisplayValue>Sliced Virtual Disk creation supported</DisplayValue>
       </PROPERTY>
       <PROPERTY NAME="CachecadeCapability" TYPE="uint8">
         <VALUE>0</VALUE>
         <DisplayValue>Cachecade Virtual Disk not supported</DisplayValue>
       </PROPERTY>
       <PROPERTY NAME="KeyID" TYPE="string">
         <DisplayValue/>
       </PROPERTY>
       <PROPERTY NAME="EncryptionCapability" TYPE="uint8">
         <VALUE>1</VALUE>
         <DisplayValue>Local Key Management Capable</DisplayValue>
       </PROPERTY>
       <PROPERTY NAME="EncryptionMode" TYPE="uint8">
         <VALUE>0</VALUE>
         <DisplayValue>None</DisplayValue>
       </PROPERTY>
       <PROPERTY NAME="SecurityStatus" TYPE="uint32">
         <VALUE>1</VALUE>
         <DisplayValue>Encryption Capable</DisplayValue>
       </PROPERTY>
       <PROPERTY NAME="SASAddress" TYPE="string">
         <VALUE>1111</VALUE>
         <DisplayValue>1111</DisplayValue>
       </PROPERTY>
       <PROPERTY NAME="ProductName" TYPE="string">
         <VALUE>PERC Mini</VALUE>
         <DisplayValue>PERC Mini</DisplayValue>
       </PROPERTY>
       <PROPERTY NAME="DeviceCardSlotType" TYPE="string">
         <VALUE>Unknown</VALUE>
         <DisplayValue>Unknown</DisplayValue>
       </PROPERTY>
       <PROPERTY NAME="DeviceCardSlotLength" TYPE="uint8">
         <VALUE>2</VALUE>
         <DisplayValue>Unknown</DisplayValue>
       </PROPERTY>
       <PROPERTY NAME="DeviceCardDataBusWidth" TYPE="string">
         <VALUE>Unknown</VALUE>
         <DisplayValue>Unknown</DisplayValue>
       </PROPERTY>
       <PROPERTY NAME="DeviceCardManufacturer" TYPE="string">
         <VALUE>DELL</VALUE>
         <DisplayValue>DELL</DisplayValue>
       </PROPERTY>
       <PROPERTY NAME="PCISubDeviceID" TYPE="string">
         <VALUE>1111</VALUE>
         <DisplayValue>1F47</DisplayValue>
       </PROPERTY>
       <PROPERTY NAME="PCIDeviceID" TYPE="string">
         <VALUE>1</VALUE>
         <DisplayValue>1</DisplayValue>
       </PROPERTY>
       <PROPERTY NAME="PCISubVendorID" TYPE="string">
         <VALUE>1</VALUE>
         <DisplayValue>1</DisplayValue>
       </PROPERTY>
       <PROPERTY NAME="PCIVendorID" TYPE="string">
         <VALUE>1</VALUE>
         <DisplayValue>1</DisplayValue>
       </PROPERTY>
       <PROPERTY NAME="Function" TYPE="string">
         <VALUE>0</VALUE>
         <DisplayValue>0</DisplayValue>
       </PROPERTY>
       <PROPERTY NAME="Device" TYPE="string">
         <VALUE>0</VALUE>
         <DisplayValue>0</DisplayValue>
       </PROPERTY>
       <PROPERTY NAME="Bus" TYPE="string">
         <VALUE>2</VALUE>
         <DisplayValue>2</DisplayValue>
       </PROPERTY>
       <PROPERTY NAME="ControllerFirmwareVersion" TYPE="string">
         <VALUE>00.00.00.00</VALUE>
         <DisplayValue>00.00.00.00</DisplayValue>
       </PROPERTY>
       <PROPERTY NAME="PCISlot" TYPE="uint8">
         <VALUE>0</VALUE>
         <DisplayValue>0</DisplayValue>
       </PROPERTY>
       <PROPERTY NAME="RollupStatus" TYPE="uint32">
         <VALUE>1</VALUE>
         <DisplayValue>OK</DisplayValue>
       </PROPERTY>
       <PROPERTY NAME="PrimaryStatus" TYPE="uint32">
         <VALUE>1</VALUE>
         <DisplayValue>OK</DisplayValue>
       </PROPERTY>
       <PROPERTY NAME="DeviceDescription" TYPE="string">
         <VALUE>Integrated RAID Controller 1</VALUE>
         <DisplayValue>Integrated RAID Controller 1</DisplayValue>
       </PROPERTY>
       <PROPERTY NAME="FQDD" TYPE="string">
         <VALUE>RAID.Integrated.1-1</VALUE>
         <DisplayValue>RAID.Integrated.1-1</DisplayValue>
       </PROPERTY>
       <PROPERTY NAME="InstanceID" TYPE="string">
         <VALUE>RAID.Integrated.1-1</VALUE>
         <DisplayValue>RAID.Integrated.1-1</DisplayValue>
       </PROPERTY>
    </INSTANCE>
</VALUE.NAMEDINSTANCE>
</SIMPLEREQ>
</MESSAGE>
</CIM>

我能够引入SQL并作为XML对象读取. 目前，我使用SSIS将XML文本放在单个字符串中.

I am able to bring into SQL and read as an XML object. Currently,I bring the XML text in a single string using SSIS.

在SSIS中读取文件后，将其带入我的数据库到一个临时表中，如下所示:

After the file is read in SSIS, it bring it into my database into a staging table like this:

现在可以将文件带到我的登台表中，我想使用触发器将它们解析为特定的列. 请注意，XML字符串使用UTF-8. 此数据来自Dell硬件清单XML文件.
他们的文档在此图中概述了他们的XML模式:

Now that the files can be brought into my staging table, I want to parse them into specific columns using a trigger. Please note that the XML string are in UTF-8. This data comes from a Dell Hardware inventory XML file.
Their document outlines their XML schema in this diagram:

我需要将XML数据转换为模仿该图的列格式.因此标题为:

I need to get that XML data into a column format that mimics that diagram. So the headers would be:

CIM |梅斯加|类名|实例ID | PropertyName |价值| DisplayValue

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

这些列将在XML中包含它们各自的数据

These columns would contain their respective data in the XML

此外，正如我提到的那样，它已转换为utf-8格式. 要将其作为SQL中的XML对象读取，我正在使用以下代码:

Also, as I mentioned it is into utf-8 format. To read it as an XML object in SQL, I am using this code:

declare @XML xml
set @XML = (SELECT Top 1 REPLACE([XML], 'utf-8', 'utf-16')FROM [StagingTable])

SELECT @XML

此外，我有多个INSTANCENAME实例，其中包含更多的INSTANCE，PROPERTY，VALUE，DISPLAYVALUE等设置.

Also, I have multiple instances of INSTANCENAME, with more sets INSTANCE, PROPERTY, VALUE, DISPLAYVALUE, etc.

以下是根据Dell文档的XML模式:

Here is the XML schema as per the Dell Document:

<?xml version="1.0" encoding="UTF-8"?>
<xs:schema targetNamespace="http://schemas.dell.com/HWinventory/1/0/events"
elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:evt="http://schemas.dell.com/HWinventory/1/0/events">
 <xs:element name="CIM">
 <xs:complexType>
 <xs:sequence>
 <xs:element name="MESSAGE">
 <xs:complexType>
 <xs:sequence>
 <xs:element name="SIMPLEREQ">
 <xs:complexType>
 <xs:sequence>
 <xs:element maxOccurs="unbounded" name="VALUE.NAMEDINSTANCE">
 <xs:complexType>
 <xs:sequence>
 <xs:element name="INSTANCENAME">
 <xs:complexType>
 <xs:sequence>
 <xs:element name="KEYBINDING">
 <xs:complexType>
 <xs:sequence>
 <xs:element name="KEYVALUE">
 <xs:complexType>
 <xs:simpleContent>
 <xs:extension base="xs:string">
 <xs:attribute name="VALUETYPE" type="xs:string" use="required" />
 </xs:extension>
 </xs:simpleContent>
 </xs:complexType>
 </xs:element>
 </xs:sequence>
<xs:attribute name="NAME" type="xs:string" use="required" />
 </xs:complexType>
 </xs:element>
 </xs:sequence>
<xs:attribute name="CLASSNAME" type="xs:string" use="required" />
 </xs:complexType>
 </xs:element>
 <xs:element name="INSTANCE">
 <xs:complexType>
 <xs:sequence>
 <xs:element minOccurs="0" name="PROPERTY.ARRAY">
 <xs:complexType>
 <xs:sequence>
 <xs:element name="VALUE.ARRAY">
 <xs:complexType>
 <xs:sequence>
 <xs:element name="VALUE" type="xs:string" />
 <xs:element name="DisplayValue" type="xs:string" />
 </xs:sequence>
 </xs:complexType>
 </xs:element>
 </xs:sequence>
<xs:attribute name="NAME" type="xs:string" use="required" />
 <xs:attribute name="TYPE" type="xs:string" use="required" />
 </xs:complexType>
 </xs:element>
<xs:element maxOccurs="unbounded" name="PROPERTY">
 <xs:complexType>
 <xs:sequence>
 <xs:element minOccurs="0" name="VALUE" type="xs:string" />
 <xs:element name="DisplayValue" type="xs:string" />
 </xs:sequence>
<xs:attribute name="NAME" type="xs:string" use="required" />
 <xs:attribute name="TYPE" type="xs:string" use="required" />
 </xs:complexType>
 </xs:element>
 </xs:sequence>
<xs:attribute name="CLASSNAME" type="xs:string" use="required" />
 </xs:complexType>
 </xs:element>
 </xs:sequence>
 </xs:complexType>
 </xs:element>
 </xs:sequence>
 </xs:complexType>
 </xs:element>
 </xs:sequence>
 <xs:attribute name="ID" type="xs:int" use="required" />
 <xs:attribute name="PROTOCOLVERSION" type="xs:decimal" use="required" />
 </xs:complexType>
 </xs:element>
 </xs:sequence>

推荐答案

以下查询将从您的XML中检索各种数据.并不是您真正需要的，但是它应该为您提供一个模板，以获取您可能想要提取的任何值:

The following query will retrieve various data from your XML. It won't be exactly what you need, but it should give you a template for any value you might want to pull out:

DECLARE @xml XML=
N'<CIM CIMVERSION="2.0" DTDVERSION="2.0">
  <MESSAGE ID="4711" PROTOCOLVERSION="1.0">
    <SIMPLEREQ>
      <VALUE.NAMEDINSTANCE xmlns:fo="http://www.w3.org/1999/XSL/Format">
        <INSTANCENAME CLASSNAME="DCIM_ControllerView">
          <KEYBINDING NAME="InstanceID">
            <KEYVALUE VALUETYPE="string">RAID.Slot.1-1</KEYVALUE>
          </KEYBINDING>
        </INSTANCENAME>
        <INSTANCE CLASSNAME="DCIM_ControllerView">
          <PROPERTY NAME="DriverVersion" TYPE="string">
            <DisplayValue />
          </PROPERTY>
          <PROPERTY NAME="KeyID" TYPE="string">
            <DisplayValue />
          </PROPERTY>
          <PROPERTY NAME="SASAddress" TYPE="string">
            <VALUE>5782BCB00C577600</VALUE>
            <DisplayValue>5782BCB00C577600</DisplayValue>
          </PROPERTY>
          <PROPERTY NAME="ProductName" TYPE="string">
            <VALUE>PERC H310 Adapter</VALUE>
            <DisplayValue>PERC H310 Adapter</DisplayValue>
          </PROPERTY>
          <PROPERTY NAME="DeviceCardSlotType" TYPE="string">
            <VALUE>PCI Express x8</VALUE>
            <DisplayValue>PCI Express x8</DisplayValue>
          </PROPERTY>
          <PROPERTY NAME="DeviceCardManufacturer" TYPE="string">
            <VALUE>DELL</VALUE>
            <DisplayValue>DELL</DisplayValue>
          </PROPERTY>
          <PROPERTY NAME="PCISubDeviceID" TYPE="string">
            <VALUE>1F4E</VALUE>
            <DisplayValue>1F4E</DisplayValue>
          </PROPERTY>
          <PROPERTY NAME="PCIDeviceID" TYPE="string">
            <VALUE>73</VALUE>
            <DisplayValue>73</DisplayValue>
          </PROPERTY>
          <PROPERTY NAME="PCISubVendorID" TYPE="string">
            <VALUE>1028</VALUE>
            <DisplayValue>1028</DisplayValue>
          </PROPERTY>
          <PROPERTY NAME="PCIVendorID" TYPE="string">
            <VALUE>1000</VALUE>
            <DisplayValue>1000</DisplayValue>
          </PROPERTY>
          <PROPERTY NAME="Function" TYPE="string">
            <VALUE>0</VALUE>
            <DisplayValue>0</DisplayValue>
          </PROPERTY>
          <PROPERTY NAME="Device" TYPE="string">
            <VALUE>0</VALUE>
            <DisplayValue>0</DisplayValue>
          </PROPERTY>
          <PROPERTY NAME="Bus" TYPE="string">
            <VALUE>1</VALUE>
            <DisplayValue>1</DisplayValue>
          </PROPERTY>
          <PROPERTY NAME="ControllerFirmwareVersion" TYPE="string">
            <VALUE>20.10.1-0066</VALUE>
            <DisplayValue>20.10.1-0066</DisplayValue>
          </PROPERTY>
        </INSTANCE>
      </VALUE.NAMEDINSTANCE>
    </SIMPLEREQ>
  </MESSAGE>
</CIM>';

-查询

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
      ,@xml.value(N'(/CIM/MESSAGE/SIMPLEREQ/VALUE.NAMEDINSTANCE/INSTANCENAME/KEYBINDING/KEYVALUE/text())[1]',N'nvarchar(max)') AS Keybinding_Value
      ,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/INSTANCE/PROPERTY') AS A(prp);

结果

+-------------+------------+------------------+---------------------------+-------------------+-------------------+
| CIM_version | Message_Id | Keybinding_Value | Prop_Name                 | Prop_Value        | Prop_DisplayValue |
+-------------+------------+------------------+---------------------------+-------------------+-------------------+
| 2.0         | 4711       | RAID.Slot.1-1    | DriverVersion             | NULL              | NULL              |
+-------------+------------+------------------+---------------------------+-------------------+-------------------+
| 2.0         | 4711       | RAID.Slot.1-1    | KeyID                     | NULL              | NULL              |
+-------------+------------+------------------+---------------------------+-------------------+-------------------+
| 2.0         | 4711       | RAID.Slot.1-1    | SASAddress                | 5782BCB00C577600  | 5782BCB00C577600  |
+-------------+------------+------------------+---------------------------+-------------------+-------------------+
| 2.0         | 4711       | RAID.Slot.1-1    | ProductName               | PERC H310 Adapter | PERC H310 Adapter |
+-------------+------------+------------------+---------------------------+-------------------+-------------------+
| 2.0         | 4711       | RAID.Slot.1-1    | DeviceCardSlotType        | PCI Express x8    | PCI Express x8    |
+-------------+------------+------------------+---------------------------+-------------------+-------------------+
| 2.0         | 4711       | RAID.Slot.1-1    | DeviceCardManufacturer    | DELL              | DELL              |
+-------------+------------+------------------+---------------------------+-------------------+-------------------+
| 2.0         | 4711       | RAID.Slot.1-1    | PCISubDeviceID            | 1F4E              | 1F4E              |
+-------------+------------+------------------+---------------------------+-------------------+-------------------+
| 2.0         | 4711       | RAID.Slot.1-1    | PCIDeviceID               | 73                | 73                |
+-------------+------------+------------------+---------------------------+-------------------+-------------------+
| 2.0         | 4711       | RAID.Slot.1-1    | PCISubVendorID            | 1028              | 1028              |
+-------------+------------+------------------+---------------------------+-------------------+-------------------+
| 2.0         | 4711       | RAID.Slot.1-1    | PCIVendorID               | 1000              | 1000              |
+-------------+------------+------------------+---------------------------+-------------------+-------------------+
| 2.0         | 4711       | RAID.Slot.1-1    | Function                  | 0                 | 0                 |
+-------------+------------+------------------+---------------------------+-------------------+-------------------+
| 2.0         | 4711       | RAID.Slot.1-1    | Device                    | 0                 | 0                 |
+-------------+------------+------------------+---------------------------+-------------------+-------------------+
| 2.0         | 4711       | RAID.Slot.1-1    | Bus                       | 1                 | 1                 |
+-------------+------------+------------------+---------------------------+-------------------+-------------------+
| 2.0         | 4711       | RAID.Slot.1-1    | ControllerFirmwareVersion | 20.10.1-0066      | 20.10.1-0066      |
+-------------+------------+------------------+---------------------------+-------------------+-------------------+

这篇关于使用SQL将XML文件解析为特定列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
数据库最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆