SQL Server 2014：使用OpenXML和BulkColumn从XML提取数据

Taie 发表于 Dev

切

我正在尝试使用OpenXML和BulkColumn从大xml文件中提取数据，然后将其保存到名为badges的新表中。我还执行一条select语句以显示表的内容。该文件存储在本地。该文件使用以属性为中心的映射，并且具有成千上万的行。我使用的代码是：

    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;

但是，当我执行上面的代码时，我得到以下结果：

这是我正在使用的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>

为什么我得到此结果而不是具有所需数据的表？

Shnugo

FROM OPENXML与相关过程一起使用来准备和删除文档已过时，不再使用。

试试这个：

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完整（最小）代码

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)

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。