Get all values for the XML file in SQL

<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

Answer

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:

LABEL	val
DefCurrency	[None]
AllowAdjs	N
IsICP	N
AllowAdjFromChildren	N
SecurityClass	NONE
UserDefined1
UserDefined2
UserDefined3
HoldingCompany
SecurityAsPartner

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:

LabelValue	LABEL	val
[None]	DefCurrency	[None]
[None]	AllowAdjs	N
[None]	IsICP	N
[None]	AllowAdjFromChildren	N
[None]	SecurityClass	NONE
[None]	UserDefined1
[None]	UserDefined2
[None]	UserDefined3
[None]	HoldingCompany
[None]	SecurityAsPartner

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

LabelValue	DefCurrency	AllowAdjs	IsICP	AllowAdjFromChildren	SecurityClass	UserDefined1	UserDefined2	UserDefined3	HoldingCompany	SecurityAsPartner
[None]	[None]	N	N	N	NONE