Reading XML files

Question

Reading XML files

ramana3327

SSCoach

Points: 19722
More actions
January 26, 2016 at 2:24 pm

#315321

Hi,
We have an xml file in network share. I am not what are the columns in that xml files to insert into a table
SELECT * into XMLwithTest FROM OPENROWSET (BULK '\etworkpath\inventory.xml', SINGLE_BLOB)
but it is failing with error "A correlation name must be specified for the bulk rowset in the from clause."

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Alan Burstein SSC Guru Points: 61141 More actions · Answer 1

ramana3327 (1/26/2016)
Hi,
We have an xml file in network share. I am not what are the columns in that xml files to insert into a table
SELECT * into XMLwithTest FROM OPENROWSET (BULK '\etworkpath\inventory.xml', SINGLE_BLOB)
but it is failing with error "A correlation name must be specified for the bulk rowset in the from clause."

You need to do something like this:

-- create the table first

CREATE TABLE XMLwithTest(XmlCol xml);

GO

INSERT XMLwithTest(XmlCol)

SELECT CONVERT(xml, BulkColumn, 2)

FROM OPENROWSET (BULK '\\etworkpath\inventory.xml', SINGLE_BLOB)

Note that there will be two backslashes (\\) in a network share. First make sure that you have the network share correct. Once that is correct you can do an INSERT statement as shown above.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

ramana3327 SSCoach Points: 19722 More actions · Answer 2

I tried the following

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 '\\Networkpath\inventory.xml', SINGLE_BLOB) AS x;

I am getting the following error

XML parsing: Line 2, character ..., illegal xml character

Any help would be appreciate.

Thanks in advance