时间戳

首页 > 解决方案 > 如何从 xml 节点中提取数据作为列中的表？

sql-server - 如何从 xml 节点中提取数据作为列中的表？

问题描述

我在 SQL Server 中有一个带有 xml 列的表，我的目标是将整个 xml 数据提取为表。

假设我的表如下所示：

CREATE TABLE Archive
(
    [Id] INT IDENTITY,
    [Timestamp] DATETIME,
    [XmlDoc] XML,
    [Description] NVARCHAR(150)
)

该XmlDoc列包含如下 XML 数据：

<Documents>
  <Doc>
    <DocId>11111</DocId>
    <Status>1</Status>
    <DocType>DriverLicense</DocType>
    <ValidDate>2022-12-31</ValidDate>
  </Doc>
  <Doc>
    <DocId>22222</DocId>
    <Status>1</Status>
    <DocType>DriverLicense</DocType>
    <ValidDate>2022-07-16</ValidDate>
  </Doc>
  <Doc>
    <DocId>33333</DocId>
    <Status>2</Status>
    <DocType>DriverLicense</DocType>
    <ValidDate>2018-11-02</ValidDate>
  </Doc>
</Documents>

我需要得到这样的表：

截屏

我尝试了类似的东西

SELECT  
    XmlDoc.value('(/Documents/Doc/DocID)[1]', 'int') as [DocID],
    XmlDoc.value('(/Documents/Doc/Status)[1]', 'int') as [Status],
    XmlDoc.value('(/Documents/Doc/DocType)[1]', 'nvarchar(30)') as [DocType],
    XmlDoc.value('(/Documents/Doc/ValidDate)[1]', 'datetime') as [ValidDate]
FROM
    dbo.Archive

但它只返回<Doc>每个 xml 的第一个节点。

是否可以将每个 Doc 节点作为单独的行？

标签： sql-serverxmltsql

解决方案

由于没有人回答我的问题，我自己找到了解决方案。您只需要交叉申请

SELECT 
    a.b.value('(DocId)[1]', 'int') as 'id', 
    a.b.value('(Status)[1]', 'int') as 'Status',
    a.b.value('(DocType)[1]', 'varchar(20)') as 'DocType',
    a.b.value('(ValidDate)[1]', 'varchar(20)') as 'ValidDate' 
FROM 
    dbo.Archive r
    CROSS APPLY r.XmlDoc.nodes('/Documents/Doc') a(b)

也许它会帮助某人。

推荐阅读