import xml file into sql server table

Question

I'm looking for help reading an XML file into a SQL Server 2008 R2 data table.

My XML file looks like this:

<?xml version="1.0" encoding="utf-8"?>
    <e_objects xmlns="http://www.blank.info/ns/2012/objects">
    <item  item_id="41-FE-001">
    <class display="true">
    <class_name>FEEDER</class_name>
    </class>
    </item>
</e_objects>

My create table SQL looks like this:

CREATE TABLE [dbo].[handover_data](
  [item_id] [nchar](15) NULL,
  [class] [nchar](10) NULL,
 ) ON [PRIMARY]
 GO

This is the SQL I'm using. I can't get it to return any values:

SELECT xmldata.value('(item[@name="item_id"]/@value)[1])', 'NCHAR') AS item_id,
xmldata.value('(class_name)', 'NCHAR') AS class       
FROM ( 
SELECT CAST(x AS XML)
FROM OPENROWSET(BULK 'C:\xmlfile.xml',
SINGLE_BLOB) AS T(x)) AS T(x)
CROSS APPLY x.nodes('e_objects/*') AS X(xmldata);

Any help would be greatly appreciated. Thanks

score 6 · Answer 1 · edited May 23 '17 at 12:23

Well, first of all - you're again, like in your last question which I already answered, ignoring the XML namespace - don't !

<e_objects xmlns="http://www.blank.info/ns/2012/objects">
           *********************************************

That's the XML namespace - it's there for a reason - don't just ignore it!

Then you're not paying attention to the structure of your XML file. Your <class_name> element is inside the <class> element and that again is inside the <item> node. So you need to select accordingly.

Also: your syntax to select the XML attribute is invalid; see my sample - does that work for you?

Try something like this:

;WITH XMLNAMESPACES(DEFAULT 'http://www.blank.info/ns/2012/objects')
SELECT 
    xmldata.value('(@item_id)[1]', 'NCHAR(10)') AS item_id,
    xmldata.value('(class/class_name)[1]', 'NCHAR(20)') AS class       
FROM 
    (SELECT CAST(x AS XML)
     FROM OPENROWSET(BULK 'C:\xmlfile.xml',
     SINGLE_BLOB) AS T(x)) AS T(x)
CROSS APPLY 
    x.nodes('/e_objects/item') AS X(xmldata);

This gets a list of all the <item> nodes under <e_objects> as X(xmldata). From those elements, I then select

the item_id attribute on the <item> node itself
the <class>/<class_name> element contained inside the <item> node

Also: I would recommend not using NCHAR too much - it's fixed-length, i.e. it's always 10 or 20 characters long - even if your string is only 2 characters. Not a good idea, most of the type - use NVARCHAR(20) instead - much better, for string lengths >= 5 !

score 1 · Answer 2 · answered Jul 24 '12 at 13:19

Create a table with an xml column to store the column. Try this, it works fine for me

    CREATE TABLE XmlImportTest
    (
        xmlFileName VARCHAR(300),
        xml_data xml
    )
    GO

    DECLARE @xmlFileName VARCHAR(300)
    SELECT  @xmlFileName = 'C:\xmlPath.xml'
    -- dynamic sql is just so we can use @xmlFileName variable in OPENROWSET
    EXEC('
    INSERT INTO XmlImportTest(xmlFileName, xml_data)

    SELECT ''' + @xmlFileName + ''', xmlData 
    FROM
    (
        SELECT  * 
        FROM    OPENROWSET (BULK ''' + @xmlFileName + ''' , SINGLE_BLOB) AS XMLDATA
    ) AS FileImport (XMLDATA)
    ')
    GO
    SELECT * FROM XmlImportTest

    DROP TABLE XmlImportTest

Reference: http://weblogs.sqlteam.com/mladenp/archive/2007/06/18/60235.aspx

import xml file into sql server table

2 Answers2