如何用SQL Server OPENXML提取XML中全部mic节点数据？

我有一段XML文本，想要提取其中的数据，编写了如下SQL Server OPENXML代码：

DECLARE @d_txt  VARCHAR(max)
SET @d_txt = '
<DI_List>
  <SDI>
    <a>6559864</a>
    <DI>
      <Id>3036780478</Id>
      <mic>
        <date>2022-11-13</date>
        <kod>774673</kod>
      </mic>
      <mic>
        <date>2022-11-11</date>
        <kod>774673</kod>
      </mic>  
    </DI>
  </SDI>
</DI_List>'

DECLARE @d_xml  INT

exec sp_xml_preparedocument @d_xml output, @d_txt;

SELECT *
FROM OPENXML(@d_xml, '/DI_List/SDI', 0) WITH
(
    Id  VARCHAR(30) 'DI/Id',
    nmic XML 'DI/mic'
)

EXEC sys.sp_xml_removedocument @d_xml

执行后得到的结果中，nmic字段仅包含第一个mic节点的数据：

但我希望nmic字段包含所有mic节点的数据，如下所示：

请问能否通过SQL Server OPENXML实现该需求？

可以通过OPENXML实现，也可以用更简洁高效的XQuery方式，以下是两种实现方案：

方案1：基于OPENXML的实现 #

通过子查询遍历所有mic节点，再用FOR XML PATH聚合为完整的XML片段：

DECLARE @d_txt  VARCHAR(max)
SET @d_txt = '
<DI_List>
  <SDI>
    <a>6559864</a>
    <DI>
      <Id>3036780478</Id>
      <mic>
        <date>2022-11-13</date>
        <kod>774673</kod>
      </mic>
      <mic>
        <date>2022-11-11</date>
        <kod>774673</kod>
      </mic>  
    </DI>
  </SDI>
</DI_List>'

DECLARE @d_xml  INT
exec sp_xml_preparedocument @d_xml output, @d_txt;

SELECT 
    sdi.Id,
    (
        SELECT mic.*
        FROM OPENXML(@d_xml, '/DI_List/SDI/DI/mic', 0) WITH (
            date DATE 'date',
            kod VARCHAR(10) 'kod'
        ) mic
        FOR XML PATH('mic'), TYPE
    ) AS nmic
FROM OPENXML(@d_xml, '/DI_List/SDI', 0) WITH
(
    Id  VARCHAR(30) 'DI/Id'
) sdi

EXEC sys.sp_xml_removedocument @d_xml

方案2：改用XQuery（推荐） #

SQL Server的XQuery功能更简洁，无需调用文档处理存储过程，直接获取所有mic节点集合：

DECLARE @d_xml XML = '
<DI_List>
  <SDI>
    <a>6559864</a>
    <DI>
      <Id>3036780478</Id>
      <mic>
        <date>2022-11-13</date>
        <kod>774673</kod>
      </mic>
      <mic>
        <date>2022-11-11</date>
        <kod>774673</kod>
      </mic>  
    </DI>
  </SDI>
</DI_List>'

SELECT 
    x.value('(DI/Id/text())[1]', 'VARCHAR(30)') AS Id,
    x.query('DI/mic') AS nmic
FROM @d_xml.nodes('/DI_List/SDI') AS t(x)

内容的提问来源于stack exchange，提问作者AZubov