CODEWITHSUNDEEP
sql-servert-sql
Voystin
Voystin

Reputation: 304

Read data from xml file T-sql

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.

query result

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?

Upvotes: 1

Views: 275

Answers (1)

Thom A
Thom A

Reputation: 95588

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.

Upvotes: 4

Related Questions