CODEWITHSUNDEEP
xmlsql-server-2008t-sqlxpathxquery
jest
jest

Reputation: 699

Issue in parsing XML in SQL Server

I am new to processing XML data in SQL Server. I came across an article (https://www.mssqltips.com/sqlservertip/2899/importing-and-processing-data-from-xml-files-into-sql-server-tables/) and tried to modify according to my XML but I am not getting any output or error. I am not sure how to proceed further and any help is appreciated. I have provided the sample below:

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

SELECT @XML = '<DataSet xmlns="http://www.example.com/">
  <diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">
    <NewDataSet xmlns="">
      <General_x0020_Report diffgr:id="General Report1" msdata:rowOrder="0">
        <Device_x0020_No>224610</Device_x0020_No>
        <License_x0020_Plate>AAA 123</License_x0020_Plate>
        <Driver>Driver Name</Driver>
        <Date_x002F_Time>2018-01-01T12:46:04+03:00</Date_x002F_Time>
      </General_x0020_Report>
     </NewDataSet>
  </diffgr:diffgram>
</DataSet>'

EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML


SELECT Device_x0020_No, License_x0020_Plate, Driver, Date_x002F_Time
FROM OPENXML(@hDoc, 'DataSet/diffgr/NewDataSet/General_x0020_Report')
WITH 
(
Device_x0020_No [varchar](50) 'Device_x0020_No',
License_x0020_Plate [varchar](100) 'License_x0020_Plate',
Driver [varchar](100) 'Driver',
Date_x002F_Time [varchar](100) 'Date_x002F_Time'
)


EXEC sp_xml_removedocument @hDoc
GO

Edit 1 : I found the issue is with "diffgr:diffgram" but not sure how to resolve the namespace issue in this query

Upvotes: 2

Views: 311

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

FROM OPENXML with the corresponding SPs to prepare and to remove a document is outdated and should not be used any more. Rather use the appropriate methods the XML data type provides.

Your XML is defining a default namespace in the first line and re-defining the default namespace as empty with <NewDataSet>. That means, that all nodes below <NewDataSet> are not living in any namespace.

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

SELECT @XML = '<DataSet xmlns="http://www.example.com/">
  <diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">
    <NewDataSet xmlns="">
      <General_x0020_Report diffgr:id="General Report1" msdata:rowOrder="0">
        <Device_x0020_No>224610</Device_x0020_No>
        <License_x0020_Plate>AAA 123</License_x0020_Plate>
        <Driver>Driver Name</Driver>
        <Date_x002F_Time>2018-01-01T12:46:04+03:00</Date_x002F_Time>
      </General_x0020_Report>
     </NewDataSet>
  </diffgr:diffgram>
</DataSet>';

--The easy approach is the deep search with //, jumping right into the nodes of interest:

SELECT @XML.value(N'(//Device_x0020_No)[1]',N'nvarchar(max)') AS DeviceNo
      ,@XML.value(N'(//License_x0020_Plate)[1]',N'nvarchar(max)') AS LicensePlate
      ,@XML.value(N'(//Driver)[1]',N'nvarchar(max)') AS Driver
      ,@XML.value(N'(//Date_x002F_Time)[1]',N'datetime') AS [DateTime];

Attention: the DateTime is shown as local time (not sure about that, test it out)

UPDATE

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

SELECT @XML = '<DataSet xmlns="http://www.example.com/">
  <diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">
    <NewDataSet xmlns="">
      <General_x0020_Report diffgr:id="General Report1" msdata:rowOrder="0">
        <Device_x0020_No>224610</Device_x0020_No>
        <License_x0020_Plate>AAA 123</License_x0020_Plate>
        <Driver>Driver Name</Driver>
        <Date_x002F_Time>2018-01-01T12:46:04+03:00</Date_x002F_Time>
      </General_x0020_Report>
      <General_x0020_Report diffgr:id="General Report1" msdata:rowOrder="0">
        <Device_x0020_No>2222</Device_x0020_No>
        <License_x0020_Plate>Some other</License_x0020_Plate>
        <Driver>One more</Driver>
        <Date_x002F_Time>2018-01-01T12:46:04+03:00</Date_x002F_Time>
      </General_x0020_Report>
     </NewDataSet>
  </diffgr:diffgram>
</DataSet>';
--The easy approach is the deep search with //, jumping right into the nodes of interest:

SELECT r.value(N'(Device_x0020_No)[1]',N'nvarchar(max)') AS DeviceNo
      ,r.value(N'(License_x0020_Plate)[1]',N'nvarchar(max)') AS LicensePlate
      ,r.value(N'(Driver)[1]',N'nvarchar(max)') AS Driver
      ,r.value(N'(Date_x002F_Time)[1]',N'datetime') AS [DateTime]
FROM @XML.nodes(N'//General_x0020_Report') AS A(r);

Upvotes: 1

Related Questions