I had a task to import some XML files that included a Base64 encoded photograph into SQL.
The data was simple enough, it was just a list of names and photographs.
<?xml version="1.0" standalone="yes"?> <dsExport xmlns=http://www.tempuri.org/dsXMLExport.xsd> <DemographicData> <FirstName>Jane</FirstName> <LastName>Doe</LastName> <Photograph>/9j/4AAQSkZJRgABAQEAYABgAAD/2wBDAAgGBgcGBQg...... </Photograph> ... <DemographicData> </dsExport>
The problem occurred when I tried any of the samples with the xmlns still in the xml data. Having a namespace here is causing the rest of the import to fail.
The third parameter to the command sp_xml_preparedocument allows for a namespace, the trick is to pick your own namespace and then use it inside of the import command.
The first step was to import the XML in it’s entirety into SQL.
USE MyDatabase GO DROP TABLE XMLwithOpenXML GO CREATE TABLE XMLwithOpenXML( Id INT IDENTITY PRIMARY KEY, XMLData XML, LoadedDateTime DATETIME) INSERT INTO XMLwithOpenXML(XMLData, LoadedDateTime) SELECT CONVERT(XML, BulkColumn) AS BulkColumn, GETDATE() FROM OPENROWSET(BULK 'D:\Data.xml', SINGLE_BLOB) AS x;
This takes the XML file and imports it into SQL as a XML database table. Doing this step separately from the rest of the steps will allow you to rerun the rest of the code without the time overhead. Also, doing this step here reduces the import time considerably.
The following code takes the XML database able and imports it into your Demographics table. The script only imports First, Last, and Photo. Adding the additional fields should be easy enough.
USE MyDatabase
GO
DECLARE @XmlDocument AS XML
DECLARE @XmlDocumentHandle AS INT
DECLARE @SQL NVARCHAR (MAX)
SELECT @XmlDocument = XMLData FROM XMLwithOpenXML
EXEC sp_xml_preparedocument @XmlDocumentHandle OUTPUT, @XmlDocument, '<ns xmlns:x="http://www.tempuri.org/dsXMLExport.xsd"/>'
INSERT INTO Demographics(First_Name, Last_Name, Photo)
SELECT FirstName, LastName, Photograph.value('.', 'varbinary(max)')
FROM OPENXML(@XmlDocumentHandle, '/x:dsXMLExport/x:VMSData', 2)
WITH(
LastName [nvarchar](255) 'x:FirstName',
FirstName [nvarchar](255) 'x:LastName',
Photograph xml
) as T
EXEC sp_xml_removedocument @XmlDocumentHandle
GO
The ‘2’ in the OpenXML statement is to allow for the fields to be elements instead of attributes.
With that, I was able to import my records into my database and was able to view the photographs.
Good luck!