SQL XML Parsing in T-SQL

Question

I have SQL Server table which has XML column that has data like below. Multiple records will be there in the table. Let us say table T1 and Column C1.

Our requirement is we need to get the latest data (by max of datereported, datecreated, dateupdated XML node for individual sections like AAA,BB,CC).

<XMLDoc>
    <AAA>
      <Name>Name_A</Name>
      <Value>Val_A</Value>
      <dateReported>1/1/2001</dateReported>
    </AAA>
    <AAA>
      <Name>Name_B</Name>
      <Value>Val_B</Value>
      <dateReported>1/1/2014</dateReported>
    </AAA>
    <AAA>
      <Name>Name_C</Name>
      <Value>Val_C</Value>
      <dateReported>1/1/2012</dateReported>
    </AAA>
    <AAA>
      <Name>Name_D</Name>
      <Value>Val_D</Value>
      <dateReported>1/1/2011</dateReported>
    </AAA>
    <BB>
        <ID>112</ID>
        <dateCreated>1/1/2011</dateCreated>

    </BB>
        <BB>
        <ID>121</ID>
        <dateCreated>1/1/2012</dateCreated>
    </BB>
    <BB>
        <ID>12</ID>
        <dateCreated>1/1/2015</dateCreated>
    </BB>
    <CC>
        <Type>XML</Type>
        <dateUpdated>1/1/2015</dateCreated>
    </CC>
     <CC>
        <Type>TXT</Type>
        <dateUpdated>3/3/2015</dateUpdated>
    </CC>
     <CC>
        <Type>XLS</Type>
        <dateUpdated>2/2/2015</dateUpdated>
    </CC>
</XMLDoc>

I have SQL written below

SELECT
        ID,
        Name = C1.value('(/XMLDoc/AAA/Name)[1]', 'varchar(100)'),
        Value = C1.value('(/XMLDoc/AAA/Value)[1]', 'varchar(100)'),
        BB_ID = C1.value('(/XMLDoc/BB/ID)[1]', 'int'), 
        CC_Type = C1.value('(/XMLDoc/CC/Type)[1]', 'varchar(50)')
From T1

We need output as

Id Name, Value, BB_ID, CC_Type
1  Name_B Val_B  12    TXT

Need your inputs

marc_s · Answer 1 · 2015-03-06T15:33:28.233

Your XML sample is horribly wrong - lots of closing tags aren't really closing tag, several don't match the opening tags, and there's a </x> in the middle of it all without any opening tag whatsoever...... next time, please try to make sure what you post is actually valid XML!

Try with this code:

DECLARE @T1 TABLE (ID INT NOT NULL, XmlContent XML)

INSERT INTO @T1 VALUES(1, '<XMLDoc>
    <AAA>
      <Name>Name_A</Name>
      <Value>Val_A</Value>
      <dateReported>1/1/2001</dateReported>
    </AAA>
    <AAA>
      <Name>Name_B</Name>
      <Value>Val_B</Value>
      <dateReported>1/1/2014</dateReported>
    </AAA>
    <AAA>
      <Name>Name_C</Name>
      <Value>Val_C</Value>
      <dateReported>1/1/2012</dateReported>
    </AAA>
    <AAA>
      <Name>Name_D</Name>
      <Value>Val_D</Value>
      <dateReported>1/1/2011</dateReported>
    </AAA>
    <BB>
        <ID>112</ID>
        <dateCreated>1/1/2011</dateCreated>
    </BB>
    <BB>
        <ID>121</ID>
        <dateCreated>1/1/2012</dateCreated>
    </BB>
    <BB>
        <ID>12</ID>
        <dateCreated>1/1/2015</dateCreated>
    </BB>
    <CC>
        <Type>XML</Type>
        <dateUpdated>1/1/2015</dateUpdated>
    </CC>
     <CC>
        <Type>TXT</Type>
        <dateUpdated>3/3/2015</dateUpdated>
    </CC>
     <CC>
        <Type>XLS</Type>
        <dateUpdated>2/2/2015</dateUpdated>
    </CC>
</XMLDoc>')

;WITH XmlData AS
(
    SELECT 
        ID,
        NodeType = 'AAA',
        RelevantDate = XC.value('(dateReported)[1]', 'datetime'),
        Name = XC.value('(Name)[1]', 'varchar(50)'),
        Value = XC.value('(Value)[1]', 'varchar(50)')
    FROM 
        @T1
    CROSS APPLY
        XmlContent.nodes('/XMLDoc/AAA') XT(XC)

    UNION

    SELECT 
        ID,
        NodeType = 'BB',
        RelevantDate = XC.value('(dateCreated)[1]', 'datetime'),
        Name = null,
        Value = XC.value('(ID)[1]', 'varchar(50)')
    FROM 
        @T1
    CROSS APPLY
        XmlContent.nodes('/XMLDoc/BB') XT(XC)

    UNION

    SELECT 
        ID,
        NodeType = 'CC',
        RelevantDate = XC.value('(dateUpdated)[1]', 'datetime'),
        Name = XC.value('(Type)[1]', 'varchar(50)'),
        Value = null
    FROM 
        @T1
    CROSS APPLY
        XmlContent.nodes('/XMLDoc/CC') XT(XC)
)
SELECT 
    xd.ID,
    Name = MAX(Name),
    Value = MAX(Value),
    BB_ID = MAX(BB_ID),
    CC_Type = MAX(CC_Type)
FROM 
    XmlData xd
WHERE
    xd.RelevantDate = (SELECT MAX(xd1.RelevantDate) FROM XmlData xd1 WHERE xd1.NodeType = xd.NodeType)
GROUP BY
    xd.ID

This gives me output of:

enter image description here

Basically, taking your XML, I'm creating a CTE (Common Table Expression) that extracts the relevant info for each of the "node types" - the <AAA> nodes, the <BB> and the <CC> nodes. Then, I can get the MAX() for the date for each of those groups by selecting from that CTE.

apologies next time shall take care of it. Your output doesn't match .. I need data nodes for the max date value whereas query gives only max date value — Saravanan, Mar 06 '15 at 14:06
Thanks but this query gives 3 rows, I want the output as single row Id Name, Value, BB_ID, CC_Type 1 Name_B Val_B 12 TXT — Saravanan, Mar 06 '15 at 14:41
@Saravanan: updated my response *yet again* - does *this* now match your needs? — marc_s, Mar 06 '15 at 15:03

score 0 · Accepted Answer · edited May 23 '17 at 11:43

I have search internet and find nice way to handle max:

This is table I used: CREATE TABLE #xmlTable (ID INT, xmlData XML)

Select:

SELECT 
    ID
    , xmlData.value ('(/XMLDoc/AAA[not(/XMLDoc/AAA/dateReported > dateReported)]/Name)[1]', 'varchar(100)') AS Name
    , xmlData.value ('(/XMLDoc/AAA[not(/XMLDoc/AAA/dateReported > dateReported)]/Value)[1]', 'varchar(100)') AS Value
    , xmlData.value ('(/XMLDoc/BB[not(/XMLDoc/BB/dateCreated > dateCreated)]/ID)[1]', 'INT') AS BB_ID
    , xmlData.value ('(/XMLDoc/CC[not(/XMLDoc/CC/dateUpdated > dateUpdated)]/Type)[1]', 'varchar(50)') AS CC_Type
FROM #xmlTable AS xt

And finally result:

ID  Name    Value   BB_ID   CC_Type  
1   Name_B  Val_B   121     TXT

The solution I found in this example https://stackoverflow.com/a/3786761/1692632

SQL XML Parsing in T-SQL

2 Answers2