davidhlynsson davidhlynsson - 5 months ago 16
SQL Question

Importing xml file in to SQL table

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.

Answer

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...