Home > Software design >  Get all values for the XML file in SQL
Get all values for the XML file in SQL

Time:05-31

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

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