Extract all attribute values from XML column in SQL Server

Question

I am trying the extract the values in a table format from the below XML.

It is originally stored as HEX, but have converted successfully, and now need to extract in a tabular forma all the values from it.

So for Example in one ROW

   BID    2

From

    <ns2:e k="BID">
            <ns2:l v="2"/>
    </ns2:e>

And so on.

    <ns2:pay xmlns:ns2="http://someurl.com/">
                <ns2:e k="BID">
                    <ns2:l v="2"/>
                </ns2:e>
                <ns2:e k="PMD">
                    <ns2:l v="1"/>
                </ns2:e>
                <ns2:e k="GPTA5">
                    <ns2:s v=""/>
                </ns2:e>
                <ns2:e k="GPTA4">
                    <ns2:s v=""/>
                </ns2:e>
                <ns2:e k="GPTA3">
                    <ns2:s v="572"/>
                </ns2:e>
                <ns2:e k="GPTA1">
                    <ns2:s v="Sweet &amp; Sour Sauce"/>
                </ns2:e>
                <ns2:e k="PFID">
                    <ns2:l v="1"/>
                </ns2:e>
                <ns2:e k="EAN">
                    <ns2:s v="010000"/>
                </ns2:e>
                <ns2:e k="PT">
                    <ns2:s v="1"/>
                </ns2:eBID>
                <ns2:e k="TXID1">
                    <ns2:l v="0"/>
                </ns2:e>
                <ns2:e k="PMN">
                    <ns2:l v="1"/>
                </ns2:e>
                <ns2:e k="DID">
                    <ns2:l v="1"/>
                </ns2:e>
                <ns2:e k="GPTA6">
                    <ns2:s v=""/>
                </ns2:e>
                <ns2:e k="GPTA7">
                    <ns2:s v=""/>
                </ns2:e>
                <ns2:e k="PLU">
                    <ns2:l v="10000"/>
                </ns2:e>
                <ns2:e k="GPTA8">
                    <ns2:s v=""/>
                </ns2:e>
                <ns2:e k="DYT">
                    <ns2:s v="SWEET &amp; SOUR SAUCE"/>
                </ns2:e>
            </ns2:payload>

Any help greatly appreciated.

https://stackoverflow.com/questions/899313/select-values-from-xml-field-in-sql-server-2008 — Hozikimaru, Nov 27 '18 at 17:40
Possible duplicate of [Select values from XML field in SQL Server 2008](https://stackoverflow.com/questions/899313/select-values-from-xml-field-in-sql-server-2008) — Hozikimaru, Nov 27 '18 at 17:40

Shnugo · Accepted Answer · 2018-11-28T14:51:15.960

I had to repair your XML, as the provided sample is not well-formed. So you might need to adapt this.

DECLARE @xml XML=
N'<ns2:pay xmlns:ns2="http://someurl.com/">
    <ns2:e k="BID">
        <ns2:l v="2"/>
    </ns2:e>
    <ns2:e k="PMD">
        <ns2:l v="1"/>
    </ns2:e>
    <ns2:e k="GPTA5">
        <ns2:s v=""/>
    </ns2:e>
    <ns2:e k="GPTA4">
        <ns2:s v=""/>
    </ns2:e>
    <ns2:e k="GPTA3">
        <ns2:s v="572"/>
    </ns2:e>
    <ns2:e k="GPTA1">
        <ns2:s v="Sweet &amp; Sour Sauce"/>
    </ns2:e>
    <ns2:e k="PFID">
        <ns2:l v="1"/>
    </ns2:e>
    <ns2:e k="EAN">
        <ns2:s v="010000"/>
    </ns2:e>
    <ns2:e k="PT">
        <ns2:s v="1"/>
    </ns2:e>
    <ns2:e k="TXID1">
        <ns2:l v="0"/>
    </ns2:e>
    <ns2:e k="PMN">
        <ns2:l v="1"/>
    </ns2:e>
    <ns2:e k="DID">
        <ns2:l v="1"/>
    </ns2:e>
    <ns2:e k="GPTA6">
        <ns2:s v=""/>
    </ns2:e>
    <ns2:e k="GPTA7">
        <ns2:s v=""/>
    </ns2:e>
    <ns2:e k="PLU">
        <ns2:l v="10000"/>
    </ns2:e>
    <ns2:e k="GPTA8">
        <ns2:s v=""/>
    </ns2:e>
    <ns2:e k="DYT">
        <ns2:s v="SWEET &amp; SOUR SAUCE"/>
    </ns2:e>
</ns2:pay>';

--This query will return all attributes with their names (a classic key-value-list)

WITH XMLNAMESPACES('http://someurl.com/' AS ns2)
SELECT e.value('@k','nvarchar(max)') AS AttributeName
      ,e.value('(ns2:l/@v)[1]','nvarchar(max)') AS AttributeValue
FROM @xml.nodes(N'/ns2:pay/ns2:e') A(e);

--This query allows you to pick the value of one given key

DECLARE @FindThis NVARCHAR(100)='BID';
WITH XMLNAMESPACES('http://someurl.com/' AS ns2)
SELECT @xml.value(N'(/ns2:pay/ns2:e[@k=sql:variable("@FindThis")]/ns2:l/@v)[1]','int'); --use the proper type, if all values will be fine with this

--This query will return a table of your values (as long as you knwo all keys in advance)

WITH XMLNAMESPACES('http://someurl.com/' AS ns2)
SELECT @xml.value(N'(/ns2:pay/ns2:e[@k="BID"]/ns2:l/@v)[1]','int') AS BID
      ,@xml.value(N'(/ns2:pay/ns2:e[@k="PMD"]/ns2:l/@v)[1]','nvarchar(max)') AS PMD
      ,@xml.value(N'(/ns2:pay/ns2:e[@k="GPTA4"]/ns2:l/@v)[1]','nvarchar(max)') AS GPTA4
--add all keys in the same way...

UPDATE

Here's an example to read this from a table using PIVOT to get it in tabular form:

Hint: I use NVARCHAR(1000) to simulate your need for a cast on-the-fly:

DECLARE @mockupTable TABLE(ID INT,YourData NVARCHAR(1000));
INSERT INTO @mockupTable VALUES
(1
,N'<ns2:pay xmlns:ns2="http://someurl.com/">
    <ns2:e k="BID">
        <ns2:l v="2"/>
    </ns2:e>
    <ns2:e k="PMD">
        <ns2:l v="1"/>
    </ns2:e>
    <ns2:e k="GPTA5">
        <ns2:s v=""/>
    </ns2:e>
    <ns2:e k="GPTA4">
        <ns2:s v=""/>
    </ns2:e>
    <ns2:e k="GPTA3">
        <ns2:s v="572"/>
    </ns2:e>
    <!--shortened for brevity-->
</ns2:pay>')
,(2
,N'<ns2:pay xmlns:ns2="http://someurl.com/">
    <ns2:e k="BID">
        <ns2:l v="20"/>
    </ns2:e>
    <ns2:e k="PMD">
        <ns2:l v="10"/>
    </ns2:e>
    <ns2:e k="GPTA5">
        <ns2:s v="bla"/>
    </ns2:e>
    <ns2:e k="GPTA4">
        <ns2:s v=""/>
    </ns2:e>
    <ns2:e k="GPTA3">
        <ns2:s v="572"/>
    </ns2:e>
    <!--shortened for brevity-->
</ns2:pay>');

--The query will create a key-value list with the row's ID as grouping factor

WITH XMLNAMESPACES('http://someurl.com/' AS ns2)
SELECT p.*
FROM
(
    SELECT ID
          ,e.value('@k','nvarchar(max)') AS AttributeName
          ,e.value('(ns2:l/@v)[1]','nvarchar(max)') AS AttributeValue
    FROM @mockupTable t
    --the cast happens here
    CROSS APPLY(SELECT CAST(t.YourData AS XML)) A(TheXml)
    --the call to .nodes() happens here to return a derived table
    CROSS APPLY TheXml.nodes(N'/ns2:pay/ns2:e') B(e)
) tbl
PIVOT(MAX(AttributeValue) 
      FOR AttributeName 
      IN(BID,PMD,GPTA3,GPTA4,GPTA5) --add your columns here, order does not matter
) p

Hi Shnugo, very helpful, i will try and spend some time to get my head around how that all works. How would I then incorporate your examples to select from a table column. So I am running SELECT ID, CAST(DATA AS XML) FROM..... first as I need to CAST from HEX. Ideally using your third example to bring back one row for each of the 30000 records. — DanBot, Nov 28 '18 at 08:38
@user3811820 Is this a one-time-action, or do you need this regularly? Do you know all keys in advance? — Shnugo, Nov 28 '18 at 09:15
Hi Shnugo, all keys will always be the same, believe it will something run occasionally when a reconcilliation is being performed. One thing i also noticed in your first example is that some fields were showing as null. But when i added ,e.value('(ns2:l/@v)[1]','nvarchar(max)') AS AttributeValue ,e.value('(ns2:s/@v)[1]','nvarchar(max)') AS AttributeValue -- added extra they are now not null. — DanBot, Nov 28 '18 at 09:23
@user3811820 I'd recommend to add a column `realXml XML` and set it to the casted data. Is this possible? — Shnugo, Nov 28 '18 at 09:28
Do not believe so, as i am not application/database owner. But if so, how would i select the xml from that column using your examples. — DanBot, Nov 28 '18 at 09:32
@user3811820 I'll update my answer later, no pc at the moment... A hint : Use cross apply first to cast the xml and another cross apply with nodes() following the first example and finally pivot to get this in tabular format... — Shnugo, Nov 28 '18 at 09:35
You sir are a legend, thankyou for taking the time to 1) answer, 2) explain in clear way so I can learn from the example for future scenarios. If you are ever in London, I owe you a beer! Cheers. — DanBot, Nov 28 '18 at 17:17

Extract all attribute values from XML column in SQL Server

1 Answers1

UPDATE