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

• Alan Burstein

  SSC Guru

  Points: 61141

  More actions

  January 26, 2016 at 3:18 pm

  #1854574

  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

  January 26, 2016 at 3:52 pm

  #1854580

  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