Read data from xml file T-sql

Question

I'm trying to read some data from xml file with T-Sql. The result I received from query are just columns name without any data.

So here is my xml file

<Xa xmlns="urn:schemas-microsoft-com:xml-data" xmlns:dt="urn:schemas-microsoft-com:datatypes">
  <HeaderInfo ReportTitle="aaa" ReportDate="7/11/2019" ReportTime="8:10:31 PM" />
  <FilterInfo FilterTitle="Source:" FilterCriteria="Primary" />
  <FilterInfo FilterTitle="Store" FilterCriteria="003" />
  <FilterInfo FilterTitle="Station" FilterCriteria="" />
  <FilterInfo FilterTitle="Receipt Date/Time" FilterCriteria="7/11/2019 12:00:00 AM to 7/11/2019 11:59:59 PM" />
  <FilterInfo FilterTitle="Workstation" FilterCriteria="3" />
  <FilterInfo FilterTitle="Cashier" FilterCriteria="" />
  <GrandTotals>
    <GrTValues GrTValTitle="Sales" GrTValSales="817.36" GrTValReturns="14.50" GrTValNet="802.86" />
    <GrTTotals GrTTotTitle="Total" GrTTotSales="817.36" GrTTotReturns="14.50" GrTTotNet="802.86" />
  </GrandTotals>
 </Xa>

and my T-sql query:

DECLARE @xmldata XML

SELECT @xmldata=BulkColumn
FROM 
OPENROWSET (BULK 'C:\my.xml',SINGLE_BLOB) as T1

DECLARE @docHandle int
EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmldata

SELECT 
    GrTValTitle, GrTValSales, GrTValReturns, GrTValNet
FROM
OPENXML(@docHandle, '/Xa/GrandTotals/GrTValues')
    WITH (GrTValTitle nvarchar(20),
        GrTValSales nvarchar(20),
        GrTValReturns nvarchar(20),
        GrTValNet nvarchar(20)
        )

Can you tell me what's wrong with my query?

I really don't recommend using sp_xml_preparedocument; you should really be using XQUERY syntax. — Thom A
– Thom A ♦, Commented Aug 19, 2019 at 13:15
Why would you use JSON_Query to query XML data, @Birel ..? — Thom A
– Thom A ♦, Commented Aug 19, 2019 at 13:33

Thom A · Accepted Answer · 2019-08-19 13:31:36Z

4

As I mentioned in the comment, you really want to use XQUERY here. sp_xml_preparedocument is a very old way of dealing with XML in SQL Server, and really shouldn't be used any more. You also need to declare your XML namespace:

DECLARE @XML xml = '<Xa xmlns="urn:schemas-microsoft-com:xml-data" xmlns:dt="urn:schemas-microsoft-com:datatypes">
  <HeaderInfo ReportTitle="aaa" ReportDate="7/11/2019" ReportTime="8:10:31 PM" />
  <FilterInfo FilterTitle="Source:" FilterCriteria="Primary" />
  <FilterInfo FilterTitle="Store" FilterCriteria="003" />
  <FilterInfo FilterTitle="Station" FilterCriteria="" />
  <FilterInfo FilterTitle="Receipt Date/Time" FilterCriteria="7/11/2019 12:00:00 AM to 7/11/2019 11:59:59 PM" />
  <FilterInfo FilterTitle="Workstation" FilterCriteria="3" />
  <FilterInfo FilterTitle="Cashier" FilterCriteria="" />
  <GrandTotals>
    <GrTValues GrTValTitle="Sales" GrTValSales="817.36" GrTValReturns="14.50" GrTValNet="802.86" />
    <GrTTotals GrTTotTitle="Total" GrTTotSales="817.36" GrTTotReturns="14.50" GrTTotNet="802.86" />
  </GrandTotals>
 </Xa>';

WITH XMLNAMESPACES(DEFAULT 'urn:schemas-microsoft-com:xml-data')
SELECT X.XMLData.value('(Xa/GrandTotals/GrTValues/@GrTValTitle)[1]','varchar(5)') AS GrTValTitle,
       X.XMLData.value('(Xa/GrandTotals/GrTValues/@GrTValSales)[1]','varchar(5)') AS GrTValSales,
       X.XMLData.value('(Xa/GrandTotals/GrTValues/@GrTValReturns)[1]','varchar(5)') AS GrTValReturns,
       X.XMLData.value('(Xa/GrandTotals/GrTValues/@GrTValNet)[1]','varchar(5)') AS GrTValNet
FROM (VALUES(@XML))X(XMLData);

So, you can turn this into one statement by doing something like this:

WITH XMLNAMESPACES(DEFAULT 'urn:schemas-microsoft-com:xml-data')
SELECT X.XMLData.value('(Xa/GrandTotals/GrTValues/@GrTValTitle)[1]','varchar(5)') AS GrTValTitle,
       X.XMLData.value('(Xa/GrandTotals/GrTValues/@GrTValSales)[1]','varchar(5)') AS GrTValSales,
       X.XMLData.value('(Xa/GrandTotals/GrTValues/@GrTValReturns)[1]','varchar(5)') AS GrTValReturns,
       X.XMLData.value('(Xa/GrandTotals/GrTValues/@GrTValNet)[1]','varchar(5)') AS GrTValNet
FROM OPENROWSET (BULK 'C:\my.xml',SINGLE_BLOB) ORS
     CROSS APPLY(VALUES(TRY_CONVERT(xml,ORS.BulkColumn))) X(XMLData);

I use TRY_CONVERT here in case the XML stored in the file cannot be converted. If that is the case, the above will return NULL for all the columns.

edited Aug 19, 2019 at 13:31

answered Aug 19, 2019 at 13:23

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

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

2 Comments

Voystin Over a year ago

thank you for your answer and explanation - this is what I needed.

Voystin Over a year ago

actually I have one more question - in this part of Select X.XMLData.value('(Xa/GrandTotals/GrTValues/@GrTValTitle)[1]','varchar(5)') AS GrTValTitle you are using [1] - this works when we want only one row. What if there is more values and I want to see all data not only first row or second [2] or third [3]

Collectives™ on Stack Overflow

Read data from xml file T-sql

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related