Issue in parsing XML in SQL Server

Question

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

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

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

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Feb 7, 2018 at 9:42

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

jest Over a year ago

Thanks! Could you please let me know how can i get all the values (for example, Driver) from the XML instead of hardcoding 1

jest Over a year ago

In order to keep the XML code simple, i provided only 1 sample data. Actually, we have close to 500 such values (500 set of General_x0020_Report)

jest Over a year ago

I thought if i can get a solution for 1 node, i can do the same for the entire XML and hence i kept the question short earlier. Thanks for pointing me in the right direction! I am new to querying XML data and learning now. Your solution helped me.

Collectives™ on Stack Overflow

Issue in parsing XML in SQL Server

1 Answer 1

UPDATE

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

UPDATE

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related