Having difficultyin parsing xml in sql server

Question

I have an XML similar to one below:

<?xml version="1.0"?>
<Entities>
    <Entity>
        <Entity_Sequence>1</Entity_Sequence>
        <Entity_Schema_Name>XYZ</Entity_Schema_Name>
        <Entity_ID>839674039</Entity_ID>
        <Entity_Name>EntName1</Entity_Name>
        <Entity_Type>1</Entity_Type>
        <Entity_Alias>X</Entity_Alias>
        <Entity_ID>839674039</Entity_ID>
        <Attributes>
            <Attribute>
                <Attribute_Sequence>1</Attribute_Sequence>
                <Attribute_Name>Attr1</Attribute_Name>
                <Filter_Operator>24002</Filter_Operator>
                <Filter_Value>12</Filter_Value>
            </Attribute>
            <Attribute>
                <Attribute_Sequence>2</Attribute_Sequence>
                <Attribute_Name>2</Attribute_Name>
                <Filter_Operator>24001</Filter_Operator>
                <Filter_Value>22</Filter_Value>
            </Attribute>
        </Attributes>
    </Entity>
</Entities>

I tried but I either get the outer nested xml or the inner one. Is it possible to use t-sql to get values like below:

You have no Attribute_ID in the nested values but you are expecting some value Nesting_Predicate_Attribute_ID. Where would you get that from? — Rigerta
– Rigerta, Commented Apr 28, 2017 at 16:34
Basically, I want to repeat the parent in each row along with the child elements. — anonxen
– anonxen, Commented Apr 28, 2017 at 16:42
Is it possible to do a third cross apply here in case we had one more hierarchy ? — anonxen
– anonxen, Commented Apr 29, 2017 at 15:08
I hope you have tried it out until now and found out that yes, you can do that. :) — Rigerta
– Rigerta, Commented May 2, 2017 at 8:35

Rigerta · Accepted Answer · 2017-04-28 16:59:40Z

1

You can try this:

SELECT 
   a.b.value('Entity_Sequence[1]','varchar(10)') AS Entity_Sequence,
   a.b.value('Entity_Schema_Name[1]','varchar(10)') AS Entity_Schema_Name,
   a.b.value('Entity_ID[1]','varchar(10)') AS Entity_ID,
   a.b.value('Entity_Name[1]','varchar(10)') AS Entity_Name,
   a.b.value('Entity_Type[1]','varchar(10)') AS Entity_Type,
   a.b.value('Entity_Alias[1]','varchar(10)') AS Entity_Alias,
   tab.col.value('Attribute_Sequence[1]', 'varchar(10)') as Attribute_Sequence,
   tab.col.value('Attribute_Name[1]', 'varchar(10)') as Attribute_Name,
   tab.col.value('Filter_Operator[1]', 'varchar(10)') as Filter_Operator,
   tab.col.value('Filter_Value[1]', 'varchar(10)') as Filter_Value    
FROM @MyXML.nodes('/Entities/Entity') a(b) cross apply a.b.nodes('Attributes/Attribute') as tab(col)

I am reading the Entity values from the main xml and then using CROSS APPLY at the Attribute node for getting the nested xml values.

You can check a working version of this here.

answered Apr 28, 2017 at 16:59

Rigerta

4,0591 gold badge17 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Alan Burstein Over a year ago

Haha! You beat me by one minute! We came up with almost identical solutions... for performance optimization you want to include the text() node reference when pulling text.

Alan Burstein · Accepted Answer · 2017-04-28 17:01:47Z

Using the information you provided (note that your screenshot does not match your data:

DECLARE @yourxml XML=
'<Entities>
    <Entity>
        <Entity_Sequence>1</Entity_Sequence>
        <Entity_Schema_Name>XYZ</Entity_Schema_Name>
        <Entity_ID>839674039</Entity_ID>
        <Entity_Name>EntName1</Entity_Name>
        <Entity_Type>1</Entity_Type>
        <Entity_Alias>X</Entity_Alias>
        <Entity_ID>839674039</Entity_ID>
        <Attributes>
            <Attribute>
                <Attribute_Sequence>1</Attribute_Sequence>
                <Attribute_Name>Attr1</Attribute_Name>
                <Filter_Operator>24002</Filter_Operator>
                <Filter_Value>12</Filter_Value>
            </Attribute>
            <Attribute>
                <Attribute_Sequence>2</Attribute_Sequence>
                <Attribute_Name>2</Attribute_Name>
                <Filter_Operator>24001</Filter_Operator>
                <Filter_Value>22</Filter_Value>
            </Attribute>
        </Attributes>
    </Entity>
</Entities>'

SELECT 
 Entity_Sequence    = ent.e.value('(Entity_Sequence/text())[1]', 'varchar(100)'),
 Entity_Schema_Name = ent.e.value('(Entity_Schema_Name/text())[1]', 'varchar(100)'),
 [Entity_ID]        = ent.e.value('(Entity_ID/text())[1]', 'varchar(100)'),
 [Entity_Name]      = ent.e.value('(Entity_Name/text())[1]', 'varchar(100)'),
 Entity_Type        = ent.e.value('(Entity_Type/text())[1]', 'varchar(100)'),
 Entity_Alias       = ent.e.value('(Entity_Alias/text())[1]', 'varchar(100)'),
 Attribute_Sequence = att.a.value('(Attribute_Sequence/text())[1]', 'varchar(100)'),
 Attribute_Name     = att.a.value('(Attribute_Name/text())[1]', 'varchar(100)'),
 Filter_Operator    = att.a.value('(Filter_Operator/text())[1]', 'varchar(100)'),
 Filter_Value       = att.a.value('(Filter_Value/text())[1]', 'varchar(100)')
FROM (VALUES (@yourxml)) t(x)
CROSS APPLY t.x.nodes('Entities/Entity') ent(e)
CROSS APPLY ent.e.nodes('Attributes/Attribute') att(a);

Results:

Entity_Sequence Entity_Schema_Name Entity_ID  Entity_Name Entity_Type Entity_Alias Attribute_Sequence Attribute_Name Filter_Operator Filter_Value
--------------- ------------------ ---------- ----------- ----------- ------------ ------------------ -------------- --------------- ------------
1               XYZ                839674039  EntName1    1           X            1                  Attr1          24002           12
1               XYZ                839674039  EntName1    1           X            2                  2              24001           22

Collectives™ on Stack Overflow

Having difficultyin parsing xml in sql server

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related