Warning: include(wp-includes/class-wp-query-update.php) [function.include]: failed to open stream: No such file or directory in /home/content/05/11443405/html/wp-settings.php on line 472
Warning: include() [function.include]: Failed opening 'wp-includes/class-wp-query-update.php' for inclusion (include_path='.:/usr/local/php5_3/lib/php') in /home/content/05/11443405/html/wp-settings.php on line 472
Warning: Cannot modify header information - headers already sent by (output started at /home/content/05/11443405/html/wp-settings.php:472) in /home/content/05/11443405/html/wp-content/themes/eclipsepro2/cyberchimps/functions.php on line 1457 Zombies With Coffee, LLC | Monthly Archives: March 2016March 2016 - Zombies With Coffee, LLC
Late night programming fueled by the power of caffeine.
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.
DROP TABLE XMLwithOpenXML
CREATE TABLE XMLwithOpenXML(
Id INT IDENTITY PRIMARY KEY,
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.
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)
LastName [nvarchar](255) 'x:FirstName',
FirstName [nvarchar](255) 'x:LastName',
) as T
EXEC sp_xml_removedocument @XmlDocumentHandle
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.