CODEWITHSUNDEEP
sql-serverxmlt-sqlsql-server-2014
Taie
Taie

Reputation: 1179

SQL Server 2014: Extracting data from XML using OpenXML and BulkColumn

I am trying to extract data from a big xml file using OpenXML and BulkColumn and then save it into a new table called badges. I am also executing a select statement to show the content of the table. The file is stored locally. The file uses the attribute-centric mapping and has tens of thousands of rows. The code I am using is:

    CREATE TABLE dbo.badges (
        Id              int,
        Name            NVARCHAR(1000),
        Date            date,
        Class           smallint,
        TagBased        nvarchar(10),

    );

    DECLARE @XMLDoc XML;    
    DECLARE @XMLDocID INT;  
    SELECT @XMLDoc = BulkColumn 
    FROM OPENROWSET(BULK 'C:\Users\Zuhair\Desktop\Badges.xml', SINGLE_BLOB);    

    EXEC sys.sp_xml_preparedocument @XMLDocID OUTPUT, @XMLDoc;

    SELECT  Id, Name, Date, Class, TagBased
    FROM    OPENXML(@XMLDocID, '/badges/row')
    WITH    (Id int 'Id',
                Name NVARCHAR(1000) 'Name',
                Date date 'Date',
                Class smallint 'Class',
                TagBased nvarchar(10) 'TagBased');

    INSERT INTO dbo.badges (Id, Name, Date, Class, TagBased)

    SELECT  *

    FROM    OPENXML(@XMLDocID, '/badges/row')
    WITH    (Id int 'Id',
                Name NVARCHAR(1000) 'Name',
                Date date 'Date',
                Class smallint 'Class',
                TagBased nvarchar(10) 'TagBased');

    exec sp_xml_removedocument @XMLDocID;

However, when I execute the above code I get the following result:

query result

Here is a sample of the XML data that I am using:

<badges>
  <row Id="1" UserId="2" Name="Autobiographer" Date="2010-08-11T18:25:03.937" Class="3" TagBased="False" />
  <row Id="2" UserId="3" Name="Autobiographer" Date="2010-08-11T18:25:03.997" Class="3" TagBased="False" />
  <row Id="3" UserId="4" Name="Autobiographer" Date="2010-08-11T18:25:04.107" Class="3" TagBased="False" />
  <row Id="4" UserId="22" Name="Autobiographer" Date="2010-08-11T19:35:05.283" Class="3" TagBased="False" />
  <row Id="5" UserId="33" Name="Autobiographer" Date="2010-08-11T19:35:05.330" Class="3" TagBased="False" />
  <row Id="6" UserId="27" Name="Autobiographer" Date="2010-08-11T19:40:05.490" Class="3" TagBased="False" />
  ...
</badges>

Why am I getting this result rather than a table that has the desired data?

Upvotes: 0

Views: 935

Answers (2)

DVT
DVT

Reputation: 3127

First and foremost, I like Shnugo's answer and I believe that is the path you should follow. For your specific question, the reason you got all the NULL is because you are extracting data from the ATTRIBUTE and you forgot all the @. Try the code below:

DECLARE @XMLDoc XML =
'<badges>
  <row Id="1" UserId="2" Name="Autobiographer" Date="2010-08-11T18:25:03.937" Class="3" TagBased="False" />
  <row Id="2" UserId="3" Name="Autobiographer" Date="2010-08-11T18:25:03.997" Class="3" TagBased="False" />
  <row Id="3" UserId="4" Name="Autobiographer" Date="2010-08-11T18:25:04.107" Class="3" TagBased="False" />
  <row Id="4" UserId="22" Name="Autobiographer" Date="2010-08-11T19:35:05.283" Class="3" TagBased="False" />
  <row Id="5" UserId="33" Name="Autobiographer" Date="2010-08-11T19:35:05.330" Class="3" TagBased="False" />
  <row Id="6" UserId="27" Name="Autobiographer" Date="2010-08-11T19:40:05.490" Class="3" TagBased="False" />
</badges>';    

DECLARE @XMLDocID INT;  
EXEC sys.sp_xml_preparedocument @XMLDocID OUTPUT, @XMLDoc;

SELECT  Id, Name, Date, Class, TagBased
FROM    OPENXML(@XMLDocID, '/badges/row')
WITH    (Id int '@Id',
            Name NVARCHAR(1000) '@Name',
            Date date '@Date',
            Class smallint '@Class',
            TagBased nvarchar(10) '@TagBased');

exec sp_xml_removedocument @XMLDocID;

Upvotes: 0

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

The usage of FROM OPENXML with the related procedures to prepare and remove a document are out-dated and should not be used any more.

Try this:

DECLARE @xml XML =
'<badges>
  <row Id="1" UserId="2" Name="Autobiographer" Date="2010-08-11T18:25:03.937" Class="3" TagBased="False" />
  <row Id="2" UserId="3" Name="Autobiographer" Date="2010-08-11T18:25:03.997" Class="3" TagBased="False" />
  <row Id="3" UserId="4" Name="Autobiographer" Date="2010-08-11T18:25:04.107" Class="3" TagBased="False" />
  <row Id="4" UserId="22" Name="Autobiographer" Date="2010-08-11T19:35:05.283" Class="3" TagBased="False" />
  <row Id="5" UserId="33" Name="Autobiographer" Date="2010-08-11T19:35:05.330" Class="3" TagBased="False" />
  <row Id="6" UserId="27" Name="Autobiographer" Date="2010-08-11T19:40:05.490" Class="3" TagBased="False" />
</badges>';    

SELECT r.value('@Id','int') AS Id
      ,r.value('@UserId','int') AS UserId
      ,r.value('@Name','varchar(max)') AS Name
      ,r.value('@Date','datetime') AS [Date]
      ,r.value('@Class','int') AS Class
      ,r.value('@TagBased','bit') AS TagBased
FROM @xml.nodes('/badges/row') AS A(r)

UPDATE The full (minimal) code

DECLARE @XMLDoc XML;        
SELECT @XMLDoc = BulkColumn 
FROM OPENROWSET(BULK 'C:\Users\Zuhair\Desktop\Badges.xml', SINGLE_BLOB) AS x;    

SELECT r.value('@Id','int') AS Id
      ,r.value('@UserId','int') AS UserId
      ,r.value('@Name','varchar(max)') AS Name
      ,r.value('@Date','datetime') AS [Date]
      ,r.value('@Class','int') AS Class
      ,r.value('@TagBased','bit') AS TagBased
FROM @XMLDoc.nodes('/badges/row') AS A(r)

Upvotes: 2

Related Questions