Parse XML file using SQL into Specific Columns

Question

I have an XML file that looks like this:








RAID.Integrated.1-1


    
       
         20170223172914.000000+000
         2017-02-23T17:29:14
       
       
         20170223172914.000000+000
         2017-02-23T17:29:14
       
       
         6
         Capable
       
       
         1
         Supported
       
       
         1
         Supported
       
       
         Generation 3
         Generation 3
       
       
         Generation 3
         Generation 3
       
       
         1
         Stopped
       
       
          Not Applicable
       
       
         0
         0 MB
       
       
         1
         Supported
       
       
         1
         Supported
       
       
         1
         Sliced Virtual Disk creation supported
       
       
         0
         Cachecade Virtual Disk not supported
       
       
         
       
       
         1
         Local Key Management Capable
       
       
         0
         None
       
       
         1
         Encryption Capable
       
       
         1111
         1111
       
       
         PERC Mini
         PERC Mini
       
       
         Unknown
         Unknown
       
       
         2
         Unknown
       
       
         Unknown
         Unknown
       
       
         DELL
         DELL
       
       
         1111
         1F47
       
       
         1
         1
       
       
         1
         1
       
       
         1
         1
       
       
         0
         0
       
       
         0
         0
       
       
         2
         2
       
       
         00.00.00.00
         00.00.00.00
       
       
         0
         0
       
       
         1
         OK
       
       
         1
         OK
       
       
         Integrated RAID Controller 1
         Integrated RAID Controller 1
       
       
         RAID.Integrated.1-1
         RAID.Integrated.1-1
       
       
         RAID.Integrated.1-1
         RAID.Integrated.1-1

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.

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

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:

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

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

These columns would contain their respective data in the 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

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

Here is the XML schema as per the Dell Document:

Gottfried Lesigang · Accepted Answer

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'
  
    
      
        
          
            RAID.Slot.1-1
          
        
        
          
            
          
          
            
          
          
            5782BCB00C577600
            5782BCB00C577600
          
          
            PERC H310 Adapter
            PERC H310 Adapter
          
          
            PCI Express x8
            PCI Express x8
          
          
            DELL
            DELL
          
          
            1F4E
            1F4E
          
          
            73
            73
          
          
            1028
            1028
          
          
            1000
            1000
          
          
            0
            0
          
          
            0
            0
          
          
            1
            1
          
          
            20.10.1-0066
            20.10.1-0066
          
        
      
    
  
';

--The query

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);

The result

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

Parse XML file using SQL into Specific Columns

Answers (1)

Related Questions