Read XML on SQL Server : Select custom attribute

Question

I'm trying to read an XML, it works pretty well on the whole except for the attribute named :

<custom-attribute attribute-id="loyaltyNumber">1234567890</custom-attribute>

which I am trying to get the value "1234567890"

Here is the test code:

DECLARE @XML XML = '<customers>
<customer customer-no="00000001">
        <credentials>
            <login>[email protected]</login>
        </credentials>
        <profile>
            <preferred-locale>fr_BE</preferred-locale>
            <custom-attributes>
                <custom-attribute attribute-id="ServiceId">1</custom-attribute>
                <custom-attribute attribute-id="loyaltyNumber">1234567890</custom-attribute>
            </custom-attributes>
        </profile>
        <note/>
    </customer>
    <customer customer-no="00000002">
        <credentials>
            <login>[email protected]</login>
        </credentials>
        <profile>
            <preferred-locale>fr_FR</preferred-locale>
            <custom-attributes>
                <custom-attribute attribute-id="loyaltyNumber">1234567890</custom-attribute>
            </custom-attributes>
        </profile>
        <note/>
    </customer>
</customers>'
 
SELECT
    CustomerNo = Events.value('@customer-no', 'int'),
    --EventType = Events.value('@Type', 'varchar(20)'),
    CustomerLogin =Events.value('(credentials/login)[1]', 'varchar(60)'),
    CustomerLocal =Events.value('(profile/preferred-locale)[1]', 'varchar(60)'),
    EventKind =Events.value('(profile/custom-attributes/custom-attribute)[2]', 'varchar(60)')
FROM
    @XML.nodes('/customers/customer') AS XTbl(Events)

The current result is:

CustomerNo	CustomerLogin	CustomerLocal	EventKind
1	[email protected]	fr_BE	1234567890
2	[email protected]	fr_FR	NULL

And it's logical since custom-attributes are optional, so you can't access them with the index. So I'm looking for a way to access them by name: attribute-id="loyaltyNumber"

Thanks,

Answer 1

..filter the path to the attribute which has attribute-id = “loyaltyNumber”

EventKind =Events.value('(profile/custom-attributes/custom-attribute[@attribute-id="loyaltyNumber"])[1]', 'varchar(60)')

Answer 2

You can just add the attribute-id of the custom attribute to your query:

DECLARE @XML XML = '<customers>
<customer customer-no="00000001">
        <credentials>
            <login>[email protected]</login>
        </credentials>
        <profile>
            <preferred-locale>fr_BE</preferred-locale>
            <custom-attributes>
                <custom-attribute attribute-id="ServiceId">1</custom-attribute>
                <custom-attribute attribute-id="loyaltyNumber">1234567890</custom-attribute>
            </custom-attributes>
        </profile>
        <note/>
    </customer>
    <customer customer-no="00000002">
        <credentials>
            <login>[email protected]</login>
        </credentials>
        <profile>
            <preferred-locale>fr_FR</preferred-locale>
            <custom-attributes>
                <custom-attribute attribute-id="loyaltyNumber">1234567777</custom-attribute>
            </custom-attributes>
        </profile>
        <note/>
    </customer>
</customers>'
 
SELECT
    CustomerNo = Events.value('@customer-no', 'int'),
    --EventType = Events.value('@Type', 'varchar(20)'),
    CustomerLogin =Events.value('(credentials/login)[1]', 'varchar(60)'),
    CustomerLocal =Events.value('(profile/preferred-locale)[1]', 'varchar(60)'),
    EventKind =Events.value('(profile/custom-attributes/custom-attribute[@attribute-id = "loyaltyNumber"])[1]', 'varchar(60)')
FROM
 @XML.nodes('/customers/customer') AS XTbl(Events)