将 XML 节点作为 SQL Server 中的行读取 [英] Read XML nodes as rows in SQL Server

问题描述

我将以下 XML 存储在表的 XML 列中:

I have the following XML stored in a XML column of a table:

<MessageResponse xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
                 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <MessageContainer>
    <DocumentType xmlns="urn:www:bebe:de:b2b:v01">Testdata</DocumentType>
    <Payload xmlns="urn:www:bebe:de:b2b:v01">
      <Testdata xmlns:ns0="un:unece:260:data:ron_data:v1">
        <PayloadResponseEvent>
          <Identification>2970729807</Identification>
          <StatusType listAgencyIdentifier="6">accepted</StatusType>
          <OriginalDocument>2769262</OriginalDocument>
        </PayloadResponseEvent>
        <PayloadResponseEvent>
           <Identification>2970729809</Identification>
          <StatusType listAgencyIdentifier="6">rejected</StatusType>
          <OriginalDocument>2769269</OriginalDocument>
        </PayloadResponseEvent>
      </Testdata>
    </Payload>
  </MessageContainer>
</MessageResponse>

我想创建一个选择，其中我根据 PayloadResponseEvent 获取列，例如:

I want to create a select in which I get columns based on PayloadResponseEvent like:

Identification        StatusType    OriginalDocument
----------------------------------------------------
2970729807            accepted      2769262
2970729809            rejected      2769269

我尝试了许多基于节点和值的不同 SQL/XML 语句，但找不到获取这些数据的简单方法.命名空间也在妨碍.

I have tried many different SQL / XML statements based on Nodes and values but cannot find an easy way of getting this data. Also namespaces are getting in the way.

有人可以提供解决方案吗?

Can anybody provide a solution to this?

谢谢

推荐答案

WITH XMLNAMESPACES (DEFAULT 'urn:www:bebe:de:b2b:v01')
SELECT 
    d.p.value('./Identification[1]', 'BIGINT') AS [Identification]
    ,d.p.value('./StatusType[1]', 'NVARCHAR(100)') AS [StatusType]
    ,d.p.value('(./StatusType/@listAgencyIdentifier)[1]', 'int') AS [ListAgencyIdentifier]
    ,d.p.value('./OriginalDocument[1]', 'BIGINT') AS [OriginalDocument]
FROM [yourtable] [t]
CROSS APPLY [t].[yourcolumn].nodes('//PayloadResponseEvent') AS d(p)

这篇关于将 XML 节点作为 SQL Server 中的行读取的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！