Importing xml file in to SQL table

Question

I have this code in a SQL query:

CREATE TABLE Products(
 P VARCHAR(30));

 Select *
 from products

 Declare @x xml

 Select @x=P
 from openrowset (BULK 'C:\xmltest.xml', Single_blob) AS Products(P)  

 Select @x

 Declare @hdoc int

 Exec sp_xml_preparedocument @hdoc OUTPUT, @x

 select *
 From openxml (@hdoc, '/reservationDetailsResponse/reservation', 2)
 with ( reservationNo int

  )

Exec sp_xml_removedocument @hdoc

where i import an xml file called "xmltest", the xml file looks like this:

<reservationDetailsResponse xmlns="http://webservices.de/webservices/franchis/1.03" xmlns:xsi="http://www.w.org/20/XMLSchema-instance">
  <reservation>
    <reservationNo>9833591183</reservationNo>
    <securityCode>ad4badfd52</securityCode>
    <reference1 xsi:nil="true" />
    <reference2 xsi:nil="true" />
    <status>RS</status>
    <revision xsi:nil="true" />
    <language>es_ES</language>
    <group>CFMR</group>
    <duration>7</duration>
    <voucherDays xsi:nil="true" />
    <isLongterm>false</isLongterm>
    <hasOwnInsurance>false</hasOwnInsurance>
    <vipStatus xsi:nil="true" />
    <customerRemark xsi:nil="true" />
    <stationRemark>PREPAID, DRIVER MUST BE (System, 23.12.2015, 18:07:07)</stationRemark>
    <flightNo>U2</flightNo>
    <bonusProgramNo xsi:nil="true" />
    <hasOnlinePaymentGuarantee>false</hasOnlinePaymentGuarantee>
  </reservation>
</reservationDetailsResponse>

This code does work for another xml file that i have, but this one is different, the results always gives a empty table column(does not show the reservationNO values). There are also many reservations(repetition) in the xml file but i only pasted the first part. It is possible that the first line in the xml file reservationDetailsResponse is causing this, but i am not sure.

Gottfried Lesigang · Accepted Answer · 2016-05-19 07:52:59Z

1

If ever possible you should try to go the set based approach with ad-hoc SQL:

Try this:

WITH XMLNAMESPACES('http://www.w.org/20/XMLSchema-instance' AS xsi
                   ,DEFAULT 'http://webservices.de/webservices/franchis/1.03')
,MyXML(XmlContent) AS
(
    SELECT CAST(P AS XML) FROM OPENROWSET (BULK 'C:\xmltest.xml', SINGLE_BLOB) AS Products(P)  
)
SELECT reservation.value('reservationNo[1]','bigint') AS reservationNo
      ,reservation.value('securityCode[1]','nvarchar(max)') AS securityCode
      ,reservation.value('reference1[1]/@xsi:nil','bit') AS reference1IsNull
      ,reservation.value('reference1[1]','nvarchar(max)') AS reference1
      --add other columns following this schema
FROM MyXML
CROSS APPLY MyXML.XmlContent.nodes('/reservationDetailsResponse/reservation') AS A(reservation)

Short explanation

You must define the namespaces in order to use them. Well, it was allowed to put a *: in front of all names (which would mean any namespace), but the general advise is: Be as specific as possible!

The BULK LOAD is done within a CTE.

This would work even with many reservation nodes in your XML due to the usage of the .nodes() method.

The result will be a table-wise resultset with the XML's content.

No need to call any stored procedures...

answered May 19, 2016 at 7:52

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

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

2 Comments

Serg Over a year ago

Consider returning NULL explicitly ,reservation.value('(reference1[not(@xsi:nil eq "true")])[1]','nvarchar(max)') AS reference1

Gottfried Lesigang Over a year ago

@Serg, you are right, that's better... One should set this [not(@xsi:nil eq "true")] to all elements which might be NULL in order to get real NULLs back. Thx!

Serg · Accepted Answer · 2016-05-18 19:01:05Z

0

It's definitley XML namespace issue. You must define default namespace explicitly ( i.e give it some name, i've gave z) for the document and specify it in select.

Exec sp_xml_preparedocument @hdoc OUTPUT, @x, '<reservationDetailsResponse xmlns:z="http://webservices.de/webservices/franchis/1.03"/>';


 select *
 From openxml (@hdoc, '/z:reservationDetailsResponse/z:reservation', 2)
  with ( reservationNo bigint 'z:reservationNo'

  )

answered May 18, 2016 at 19:01

Serg

22.8k5 gold badges23 silver badges50 bronze badges

2 Comments

Serg Over a year ago

@Shnugo but not deprecated. All old applications needn't worry it about.

Gottfried Lesigang Over a year ago

Well, it's working... And there are very rare situations where it might be the right choice. But I would not advise this approach...

Collectives™ on Stack Overflow

Importing xml file in to SQL table

2 Answers 2

Short explanation

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Short explanation

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related