Hi Matthew,

and thank you for this article.

Few comments, firstly the sp_xml_preparedocument and sp_xml_removedocument have some limitations and quite an overhead, I'm very hesitant recommending / implementing them on a production system. XQuery on the XML data type is without those limitations and in terms of importing XML data, only bound by the 2Gb size limit of the XML variable.

Secondly, the SQL Server uses UTF-16 as the encoding for the XML data type, casting from any other encoding will fail with an error. To bypass this, use OPENROWSET(BULK, [FILE_PATH], SINGLE_BLOB) without any typecast, to directly load the data into the destination table as this ignores any encoding specified in the source file.

Import example using XQuery:

USE tempdb;

GO

DECLARE @XMLTABLE TABLE

(

XML_ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL

,XML_DATA XML NOT NULL

);

INSERT INTO @XMLTABLE (XML_DATA)

SELECT BulkColumn FROM OPENROWSET

(

BULK

'C:\ImportXML\XMLDATA01.xml'

,SINGLE_BLOB

) AS IMPXML

UNION ALL

SELECT BulkColumn FROM OPENROWSET

(

BULK

'C:\ImportXML\XMLDATA02.xml'

,SINGLE_BLOB

) AS IMPXML

SELECT

XT.XML_ID

,PATI.ENT.value('id[1]' ,'INT') AS PATIENT_ID

,PATI.ENT.value('name[1]' ,'NVARCHAR(128)') AS PATIENT_NAME

,PATI.ENT.value('birthdate[1]','DATE') AS PATIENT_BIRTHDATE

FROM @XMLTABLE XT

OUTER APPLY XT.XML_DATA.nodes('root/patient') AS PATI(ENT);

😎