在SQL中獲取XML檔案的所有值

<MEMBER>
                <LABEL>[None]</LABEL>
                <AT Name="DefCurrency">[None]</AT>
                <AT Name="AllowAdjs">N</AT>
                <AT Name="IsICP">N</AT>
                <AT Name="AllowAdjFromChildren">N</AT>
                <AT Name="SecurityClass">NONE</AT>
                <AT Name="UserDefined1"> </AT>
                <AT Name="UserDefined2"> </AT>
                <AT Name="UserDefined3"> </AT>
                <AT Name="HoldingCompany"></AT>
                <AT Name="SecurityAsPartner"></AT>
                <DEFAULTPARENT>#root</DEFAULTPARENT>
                <DESCRIPTION Language="English">None</DESCRIPTION>
</MEMBER>

我試過了@Name，我得到了所有的值，比如 DefCurrency、IsICP 等，但我沒有得到這些值。

請參閱下面的代碼。

DECLARE @XML AS XML, @hDoc AS INT
SELECT @XML = XMLData FROM XMLwithOpenXML

EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML

SELECT      
            LABEL,
        
            val

FROM OPENXML(@hDoc, '/MEMBER/AT')
WITH 
(
LABEL [varchar](50) '@Name',

val [varchar](50) '../AT'


)

提前致謝

uj5u.com熱心網友回復：

所有受支持的 Microsoft SQL Server 版本都具有可用于查詢 XML 資料型別的nodes()和value()方法，例如：

declare @xml xml = '<MEMBER>
                <LABEL>[None]</LABEL>
                <AT Name="DefCurrency">[None]</AT>
                <AT Name="AllowAdjs">N</AT>
                <AT Name="IsICP">N</AT>
                <AT Name="AllowAdjFromChildren">N</AT>
                <AT Name="SecurityClass">NONE</AT>
                <AT Name="UserDefined1"> </AT>
                <AT Name="UserDefined2"> </AT>
                <AT Name="UserDefined3"> </AT>
                <AT Name="HoldingCompany"></AT>
                <AT Name="SecurityAsPartner"></AT>
                <DEFAULTPARENT>#root</DEFAULTPARENT>
                <DESCRIPTION Language="English">None</DESCRIPTION>
</MEMBER>';

select
  at.value(N'@Name', N'nvarchar(128)') as LABEL,
  at.value(N'(text())[1]', N'nvarchar(128)') as val
from @xml.nodes(N'/MEMBER/AT') member(at);

產生輸出：

正如@Charlieface 提到的，您還可以直接查詢您的表并cross apply使用以下nodes()方法：

select
  at.value(N'@Name', N'nvarchar(128)') as LABEL,
  at.value(N'(text())[1]', N'nvarchar(128)') as val
from XMLwithOpenXML
cross apply XMLData.nodes(N'/MEMBER/AT') member(at);

可以堆疊多層cross apply ... .nodes()，因此要包含LABEL元素的內容，您可以修改代碼如下：

select
  label.value(N'(text())[1]', 'nvarchar(128)') as LabelValue,
  at.value(N'@Name', N'nvarchar(128)') as LABEL,
  at.value(N'(text())[1]', N'nvarchar(128)') as val
from XMLwithOpenXML
cross apply XMLData.nodes(N'/MEMBER') nodes1(member)
cross apply member.nodes(N'LABEL') nodes2(label)
cross apply member.nodes(N'AT') nodes3(at);

產生輸出：

但是，您很可能不希望LABEL在多行中重復元素的值，因此您可以改為使用 XQuery 過濾Name屬性并使用以下內容有效地旋轉資料：

select
  label.value(N'(text())[1]', 'nvarchar(128)') as LabelValue,
  member.value(N'(AT[@Name="DefCurrency"]/text())[1]', N'nvarchar(128)') as DefCurrency,
  member.value(N'(AT[@Name="AllowAdjs"]/text())[1]', N'nvarchar(128)') as AllowAdjs,
  member.value(N'(AT[@Name="IsICP"]/text())[1]', N'nvarchar(128)') as IsICP,
  member.value(N'(AT[@Name="AllowAdjFromChildren"]/text())[1]', N'nvarchar(128)') as AllowAdjFromChildren,
  member.value(N'(AT[@Name="SecurityClass"]/text())[1]', N'nvarchar(128)') as SecurityClass,
  member.value(N'(AT[@Name="UserDefined1"]/text())[1]', N'nvarchar(128)') as UserDefined1,
  member.value(N'(AT[@Name="UserDefined2"]/text())[1]', N'nvarchar(128)') as UserDefined2,
  member.value(N'(AT[@Name="UserDefined3"]/text())[1]', N'nvarchar(128)') as UserDefined3,
  member.value(N'(AT[@Name="HoldingCompany"]/text())[1]', N'nvarchar(128)') as HoldingCompany,
  member.value(N'(AT[@Name="SecurityAsPartner"]/text())[1]', N'nvarchar(128)') as SecurityAsPartner
from XMLwithOpenXML
cross apply XMLData.nodes(N'/MEMBER') nodes1(member)
cross apply member.nodes(N'LABEL') nodes2(label);

標籤：sql sql服务器 xml

上一篇：獲取上個月的日期范圍

下一篇：查詢以獲取所需格式的輸出