sql-serverxmlt-sql

SQL script to import a XML file

I'm trying to import an XML file into a SQL Server table, there are no errors, but all values are 0 instead of the actual values.

Could you please help me to understand what is wrong with my script?

SELECT
   MY_XML.ITEM.query('.').value('.', 'bigint'),
   MY_XML.ITEM.query('PARENTID').value('.', 'bigint'),
   MY_XML.ITEM.query('CHILDID').value('.', 'bigint'),
   MY_XML.ITEM.query('CHANGEID').value('.', 'bigint')
FROM 
    (SELECT 
         CAST(MY_XML AS xml)
     FROM 
         OPENROWSET(BULK 'E:\Proger\GAR\xml\19\ADDR_OBJ.XML', SINGLE_BLOB) AS T(MY_XML)) AS T(MY_XML)
CROSS APPLY 
    MY_XML.nodes('ITEMS/ITEM') AS MY_XML (ITEM);

XML file content:

<?xml version="1.0" encoding="utf-8"?>
<ITEMS>
    <ITEM ID="1713" PARENTID="233283" CHILDID="233310" CHANGEID="601851" />
    <ITEM ID="1714" PARENTID="233363" CHILDID="233369" CHANGEID="602057" />
</ITEMS>
Solution

  • Those things you want to read are attributes - not XML elements - therefore you need to prefix those names with @.

    Also: use the .value() call directly, skip that unnecessary .query(...).value().

    Try this code:

    SELECT
   MY_XML.ITEM.value('@ID', 'bigint'),
   MY_XML.ITEM.value('@PARENTID', 'bigint'),
   MY_XML.ITEM.value('@CHILDID', 'bigint'),
   MY_XML.ITEM.value('@CHANGEID', 'bigint')
FROM 
    (SELECT 
         CAST(MY_XML AS xml)
     FROM 
         OPENROWSET(BULK 'E:\Proger\GAR\xml\19\ADDR_OBJ.XML', SINGLE_BLOB) AS T(MY_XML)) AS T(MY_XML)AS T(MY_XML)
CROSS APPLY 
    MY_XML.nodes('ITEMS/ITEM') AS MY_XML (ITEM);

    This returns a result of:

    enter image description here