<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>

I have tried the @Name and I get all the values such as DefCurrency, IsICP etc but I don't get the values.

See my code below.

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'


)

Thanks in advance

CodePudding user response：

All supported versions of Microsoft SQL Server have the nodes() and value() methods available for querying the XML data type, e.g.:

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);

Which yields the output:

As mentioned by @Charlieface you can also query your table directly and cross apply to use the nodes() method:

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);

It is possible to stack multiple layers of cross apply ... .nodes(), so to include the content of the LABEL element you can modify the code like the following:

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);

Which yields the output:

In all likelihood, though, you're not wanting to repeat the LABEL element's value across multiple rows so you can instead use XQuery to filter on the Name attributes and effectively pivot the data with the following:

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);