Search code examples
sql-serverxml-parsingsqlxml

SQL Server - XML Load Issue

'Hi, i am trying to create automated XML load into SQL and initially I need to create the table with right data set. Ultimately i am trying to get Serial Number, IP Address and Timezone with all count values for "From to "to" time stamp Here is how my XML looks like:

<response xmlns="http://www.test.com/sensor-api/v2">
  <sensor-time timezone="America/New_York">2017-07-18T15:45:03-04:00</sensor-time>
  <status>
    <code>OK</code>
  </status>
  <sensor-info>
    <serial-number>Q8:80:39:60:9Z:F2</serial-number>
    <ip-address>192.167.130.18</ip-address>
    <name>Test</name>
    <group />
    <device-type>PC2 - UL</device-type>
  </sensor-info>
  <content>
    <elements>
      <element>
        <element-id>2</element-id>
        <element-name>Conf_Lower_Zone</element-name>
        <sensor-type>SINGLE_SENSOR</sensor-type>
        <data-type>ZONE</data-type>
        <from>2017-07-18T15:40:00-04:00</from>
        <to>2017-07-18T15:45:00-04:00</to>
        <resolution>ONE_MINUTE</resolution>
          <measurements>
            <measurement>
              <from>2017-07-18T15:40:00-04:00</from>
              <to>2017-07-18T15:41:00-04:00</to>
              <values>
                <value label="count">0</value>
              </values>
            </measurement>
          <measurement>
              <from>2017-07-18T15:41:00-04:00</from>
              <to>2017-07-18T15:42:00-04:00</to>
               <values>
                  <value label="count">0</value>
               </values>
          </measurement>

And Here is my SQL Part:

CREATE DATABASE OPENXMLTesting
GO

USE OPENXMLTesting
GO

CREATE TABLE XMLwithOpenXML
(
Id INT IDENTITY PRIMARY KEY,
XMLData XML,
LoadedDateTime DATETIME
)

INSERT INTO XMLwithOpenXML(XMLData, LoadedDateTime)
SELECT CONVERT(XML, BulkColumn) AS BulkColumn, GETDATE() 
FROM OPENROWSET(BULK 'C:\Example\Test.xml', SINGLE_BLOB) AS x;

SELECT * FROM XMLwithOpenXML

USE OPENXMLTesting
GO

DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)

SELECT @XML = XMLData FROM XMLwithOpenXML

EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML

SELECT [element-id], [element-name], [data-type], [ip-address]

FROM OPENXML(@hDoc, 
'content/elements/element/measurements/measurement/values/value')

WITH 
(   
    [element-id] varchar (100) '@ElementID',
    [element-name] varchar(50) '@Element_Name',
    [data-type] varchar(50) '@Data_Type',
    [ip-address] varchar(50) '@IP_Address'
)

EXEC sp_xml_removedocument @hDoc
GO

The problem is that as results i am getting only headers without any data under it. See the pic below. enter image description here

What am i doing wrong?

Solution

  • There are two issues...

    First of all there is a namespace involved. As it is the default namespace you have to declare or wildcard it.

    Secondly the chosen approach with FROM OPENXML together with the two stored procedures to create and to remove a document is completely outdated. You should use the modern native XML methods. Try this:

    DECLARE @xml XML=
N'<response xmlns="http://www.test.com/sensor-api/v2">
  <sensor-time timezone="America/New_York">2017-07-18T15:45:03-04:00</sensor-time>
  <status>
    <code>OK</code>
  </status>
  <sensor-info>
    <serial-number>Q8:80:39:60:9Z:F2</serial-number>
    <ip-address>192.167.130.18</ip-address>
    <name>Test</name>
    <group />
    <device-type>PC2 - UL</device-type>
  </sensor-info>
  <content>
    <elements>
      <element>
        <element-id>2</element-id>
        <element-name>Conf_Lower_Zone</element-name>
        <sensor-type>SINGLE_SENSOR</sensor-type>
        <data-type>ZONE</data-type>
        <from>2017-07-18T15:40:00-04:00</from>
        <to>2017-07-18T15:45:00-04:00</to>
        <resolution>ONE_MINUTE</resolution>
        <measurements>
          <measurement>
            <from>2017-07-18T15:40:00-04:00</from>
            <to>2017-07-18T15:41:00-04:00</to>
            <values>
              <value label="count">0</value>
            </values>
          </measurement>
          <measurement>
            <from>2017-07-18T15:41:00-04:00</from>
            <to>2017-07-18T15:42:00-04:00</to>
            <values>
              <value label="count">0</value>
            </values>
          </measurement>
        </measurements>
      </element>
    </elements>
  </content>
</response>';

    --The query needs to declare the namespace
    --I'll use direct calls to get the singular values
    --...and .nodes() to get a derived set of 1:n related elements

    WITH XMLNAMESPACES(DEFAULT 'http://www.test.com/sensor-api/v2')
SELECT @xml.value('(/response/sensor-time/@timezone)[1]','nvarchar(max)') AS SensortTime_TimeZone
      ,@xml.value('(/response/sensor-time/text())[1]','datetime') AS SensortTime
      ,@xml.value('(/response/status/code/text())[1]','nvarchar(max)') AS StatusCode
      ,@xml.value('(/response/sensor-info/serial-number/text())[1]','nvarchar(max)') AS SerialNumber
      ,e.value('(element-id/text())[1]','int') AS ElementId
      ,e.value('(element-name/text())[1]','nvarchar(max)') AS ElementName
      --more from here
      ,m.value('(from/text())[1]','datetime') AS Measurement_From
      ,m.value('(to/text())[1]','datetime') AS Measurement_To
      ,m.value('(values/value/@label)[1]','nvarchar(max)') AS Measurement_Label
      ,m.value('(values/value/text())[1]','int') AS Measurement_Label
FROM @xml.nodes('/response/content/elements/element') A(e)
OUTER APPLY e.nodes('measurements/measurement') B(m);