SQL Server XQuery - iterate dynamic xml and return custom structure

Question

I have below XML in SQL Server and wants to iterate over it in order to generate a structured message.

<aggregate type="ApplicationForm">
    <entity type="Form" root="true" id="d799728b-7973-4046-b60f-cb25d4ee385c">
        <attribute name="creationDate" multivalue="false">2017-01-16</attribute>
        <attribute name="product" multivalue="false">Abc</attribute>
        <relation name="r_PersonMain" multivalue="false">4808f654-f480-412c-8dc5-d29c6c811602</relation>         
        <relation name="r_PersonPayer" multivalue="false">a8e9eaf2-56a5-4f88-955b-19eb98f6e882</relation>
    </entity>
    <entity type="Payment" root="true" id="e197bf66-1e35-42a9-bdc0-0674e3a0f765">
        <attribute name="totalAnnualPremium" multivalue="false">328415.81</attribute>
        <relation name="r_PaymentMethodRecurring" multivalue="false">b8b3c652-b886-44aa-a75b-b2d3ecd6c064</relation>
        <relation name="r_PaymentMethodFirst" multivalue="false">f3d91f99-ff6a-4888-a663-24e42ecc7342</relation>
        <attribute name="term" multivalue="false">01</attribute>         
    </entity>
    <entity type="Person" root="false" id="4808f654-f480-412c-8dc5-d29c6c811602">
        <relation name="r_AddressWork" multivalue="false">cae83657-47c2-49bd-a588-7685271c4766</relation>
        <attribute name="idNumber" multivalue="false">1112223334447</attribute>  
        <relation name="r_SelectionItem" multivalue="true">...</relation>
        <relation name="r_Health" multivalue="false">07d08bd6-ec73-4710-9de4-23435cd2b088</relation>     
        <relation name="r_AddressCurrent" multivalue="false">56d17bda-e332-497e-8e22-e7b7f09f996d</relation>
        <attribute name="lastName" multivalue="false"> 1</attribute>         
        <attribute name="jobDescription1" multivalue="false"/>
        <relation name="r_Behavior" multivalue="false">2db2c23a-37dd-4857-87b4-005aa87b2c2d</relation>       
        <attribute name="email" multivalue="false"></attribute>      
        <relation name="r_AddressRegistered" multivalue="false">ce79a468-fb26-4996-91a8-82954d960855</relation>
        <attribute name="telephoneExtention1" multivalue="false"/>
        <relation name="r_Occupation1" multivalue="false">b7b69acc-2945-4f64-8ffd-4537849280f5</relation>        
    </entity>
</aggregate>

The top element contains long list of entity tags. Each of it contains two elements, attribute & relation. The attribute contains direct values while the relation contains reference to another entity tag which in turn contains either attribute or relation.

In order to pick out entity Form (1st tag), it requires to iterate on it and all of its references until all referenced entities are retrieved.

I can retrieve first entity and its relations (reference entities) and then I access its 2nd level referenced entity and then i again check inside of it if there is another "relation" tag then I have to access it as well.

Problem is, this approach is not dynamic and can not automatically retrieve all referenced items.

Q:- How I can dynamically access all referenced entities and its attributes until there is no referenced entity anymore.

Q:- I also want to give assign tag my custom names based on each attribute or entity "name" tag. for example, creationDate, product.

Here is my query.

DECLARE @xml xml
  SET @xml =(Select CAST( CAST([AAHAD].[dbo].[aq_aggregate].data AS NVARCHAR(MAX)) AS XML ) 
        FROM  [AAHAD].[dbo].[aq_aggregate]  
        WHERE [AAHAD].[dbo].[aq_aggregate].[aggregateId] = 2 
        FOR XML RAW, TYPE)

 SELECT @xml.query('
    let $xml := (/row/aggregate)
    let $form := (/row/aggregate/entity[@type="Form"])

    return
    <Form>
        <attributes>
        {
            for $form_attrs in ($form/attribute)
            return  <attribute><name>{ data($form_attrs/@name) } </name><value>{ data($form_attrs) }</value></attribute>
        }
        </attributes>
        <relations>
        {
             for $form_rel in ($form/relation)
             let $form_rel_id := data($form_rel)
             let $relation :=($xml/entity[@id=$form_rel_id])

             return  
             <relation>           
                  <attributes>
                    {
                        for $innerRel_attrs in ($relation/attribute)
                        return  <attribute><name>{ data($innerRel_attrs/@name) } </name><value>{ data($innerRel_attrs) }</value></attribute>
                    }
                   </attributes>
                   <relations>
                    {
                         for $innerRel_rel in ($relation/relation)
                         let $inner_Rel_id := data($innerRel_rel)
                         let $inner_Relation :=($xml/entity[@id=$inner_Rel_id])

                         return  
                         <relation>           
                              <attributes>
                                {
                                    for $inner2Rel_attrs in ($inner_Relation/attribute)
                                    return  <attribute><name>{ data($inner2Rel_attrs/@name) } </name><value>{ data($inner2Rel_attrs) }</value></attribute>
                                }
                               </attributes>
                               <relations>
                                {
                                     for $inner2Rel_rel in ($inner_Relation/relation)                         
                                     return <relation>{ ($inner2Rel_rel) }</relation>
                                }
                                </relations>               
                         </relation>
                    }
                    </relations>               
             </relation>
         }
        </relations>         
    </Form>
  ')

score 0 · Answer 1 · answered Mar 05 '17 at 18:40

Not sure if this is what you are looking for, but I'll use a Table-Valued-Function to dynamically parse virtually any XML structure into a hierarchy of Elements, Attributes, and Values. The results may be processed and/or pivoted via the range keys (R1/R2) or XPath.

The original source was http://beyondrelational.com/modules/2/blogs/28/posts/10495/xquery-lab-58-select-from-xml.aspx Only made a few tweaks from the original.

The performance is respectable. Your sample XML was processed in 170 ms.

Since you are on 2008, I should note that you would have to edit the CONCAT() portions.

Example:

Select * From [dbo].[udf-XML-Hier](@XML) Order By R1

Returns

The UDF if Interested

CREATE FUNCTION [dbo].[udf-XML-Hier](@XML xml)

Returns Table 
As Return

with  cte0 as (Select Lvl       = 1
                     ,ID        = Cast(1 as int) 
                     ,Pt        = Cast(NULL as int)
                     ,Element   = x.value('local-name(.)','varchar(100)')
                     ,Attribute = cast('' as varchar(100))
                     ,Value     = x.value('text()[1]','varchar(max)')
                     ,XPath     = cast(concat(x.value('local-name(.)','varchar(100)'),'[' ,cast(Row_Number() Over(Order By (Select 1)) as int),']') as varchar(1000))
                     ,Seq       = cast(1000000+Row_Number() over (Order by (Select NULL)) as varchar(1000))
                     ,AttData   = x.query('.') 
                     ,XMLData   = x.query('*') 
               From   @XML.nodes('/*') a(x) 
               Union  All
               Select Lvl       = p.Lvl + 1 
                     ,ID        = Cast( (Lvl + 1) * 1024 + (Row_Number() Over(Order By (Select 1)) * 2) as int ) * 10
                     ,Pt        = p.ID
                     ,Element   = c.value('local-name(.)','varchar(100)')
                     ,Attribute = cast('' as varchar(100))
                     ,Value     = cast( c.value('text()[1]','varchar(max)') as varchar(max) ) 
                     ,XPath     = cast(concat(p.XPath,'/',c.value('local-name(.)','varchar(100)'),'[',cast(Row_Number() Over(PARTITION BY c.value('local-name(.)','varchar(100)') Order By (Select 1)) as int),']') as varchar(1000) )
                     ,Seq       = cast(concat(p.Seq,' ',10000000+Cast( (Lvl + 1) * 1024 + (Row_Number() Over(Order By (Select 1)) * 2) as int ) * 10) as varchar(1000))
                     ,AttData   = c.query('.') 
                     ,XMLData   = c.query('*') 
               From   cte0 p 
               Cross  Apply p.XMLData.nodes('*') b(c) )
    , cte1 as (Select R1 = Row_Number() over (Order By Seq),A.*
               From  (Select  Lvl,ID,Pt,Element,Attribute,Value,XPath,Seq From cte0
                      Union All
                      Select Lvl       = p.Lvl+1
                            ,ID        = p.ID + Row_Number() over (Order By (Select NULL)) 
                            ,Pt        = p.ID
                            ,Element   = p.Element
                            ,Attribute = x.value('local-name(.)','varchar(100)')
                            ,Value     = x.value('.','varchar(max)')
                            ,XPath     = p.XPath + '/@' + x.value('local-name(.)','varchar(100)')
                            ,Seq       = cast(concat(p.Seq,' ',10000000+p.ID + Row_Number() over (Order By (Select NULL)) ) as varchar(1000))
                      From   cte0 p 
                      Cross  Apply AttData.nodes('/*/@*') a(x) 
                     ) A )
Select A.R1
      ,R2  = IsNull((Select max(R1) From cte1 Where Seq Like A.Seq+'%'),A.R1)
      ,A.Lvl
      ,A.ID
      ,A.Pt
      ,A.Element
      ,A.Attribute
      ,A.XPath
      ,Title = Replicate('|---',Lvl-1)+Element+case when Attribute='' then '' else '@'+Attribute end
      ,A.Value
 From  cte1 A

/*
Source: http://beyondrelational.com/modules/2/blogs/28/posts/10495/xquery-lab-58-select-from-xml.aspx

Declare @XML xml='<person><firstname preferred="Annie" nickname="BeBe">Annabelle</firstname><lastname>Smith</lastname></person>'
Select * from [dbo].[udf-XML-Hier](@XML) Order by R1
*/

score 0 · Answer 2 · answered Mar 06 '17 at 07:59

You tell us that you want to

generate a structued message

It would help to show the expected output...

The following code will extract all data of your XML in one easy to query derived table.

DECLARE @xml XML=
N'<aggregate type="ApplicationForm">
    <entity type="Form" root="true" id="d799728b-7973-4046-b60f-cb25d4ee385c">
        <attribute name="creationDate" multivalue="false">2017-01-16</attribute>
        <attribute name="product" multivalue="false">Abc</attribute>
        <relation name="r_PersonMain" multivalue="false">4808f654-f480-412c-8dc5-d29c6c811602</relation>         
        <relation name="r_PersonPayer" multivalue="false">a8e9eaf2-56a5-4f88-955b-19eb98f6e882</relation>
    </entity>
    <entity type="Payment" root="true" id="e197bf66-1e35-42a9-bdc0-0674e3a0f765">
        <attribute name="totalAnnualPremium" multivalue="false">328415.81</attribute>
        <relation name="r_PaymentMethodRecurring" multivalue="false">b8b3c652-b886-44aa-a75b-b2d3ecd6c064</relation>
        <relation name="r_PaymentMethodFirst" multivalue="false">f3d91f99-ff6a-4888-a663-24e42ecc7342</relation>
        <attribute name="term" multivalue="false">01</attribute>         
    </entity>
    <entity type="Person" root="false" id="4808f654-f480-412c-8dc5-d29c6c811602">
        <relation name="r_AddressWork" multivalue="false">cae83657-47c2-49bd-a588-7685271c4766</relation>
        <attribute name="idNumber" multivalue="false">1112223334447</attribute>  
        <relation name="r_SelectionItem" multivalue="true">...</relation>
        <relation name="r_Health" multivalue="false">07d08bd6-ec73-4710-9de4-23435cd2b088</relation>     
        <relation name="r_AddressCurrent" multivalue="false">56d17bda-e332-497e-8e22-e7b7f09f996d</relation>
        <attribute name="lastName" multivalue="false"> 1</attribute>         
        <attribute name="jobDescription1" multivalue="false"/>
        <relation name="r_Behavior" multivalue="false">2db2c23a-37dd-4857-87b4-005aa87b2c2d</relation>       
        <attribute name="email" multivalue="false"></attribute>      
        <relation name="r_AddressRegistered" multivalue="false">ce79a468-fb26-4996-91a8-82954d960855</relation>
        <attribute name="telephoneExtention1" multivalue="false"/>
        <relation name="r_Occupation1" multivalue="false">b7b69acc-2945-4f64-8ffd-4537849280f5</relation>        
    </entity>
</aggregate>';

--The query

WITH AllValues AS
(
    SELECT @xml.value(N'(/aggregate/@type)[1]',N'nvarchar(max)') AS Aggregate_Type
          ,e.value(N'@type',N'nvarchar(max)') AS Entity_Type
          ,e.value(N'@root',N'bit') AS Entity_Root
          ,e.value(N'@id',N'uniqueidentifier') AS [Entity_Id]
          ,nd.value(N'local-name(.)',N'nvarchar(max)') AS Node_Type
          ,nd.value(N'@name',N'nvarchar(max)') AS Node_Name
          ,nd.value(N'@multivalue',N'bit') AS Node_MultiValue
          ,nd.value(N'text()[1]',N'nvarchar(max)') AS Node_Content
    FROM @xml.nodes(N'/aggregate/entity') AS A(e)
    CROSS APPLY e.nodes(N'*') AS B(nd)
)
SELECT * FROM AllValues;

The result (named and (if possible) typed):

+-----------------+-------------+-------------+--------------------------------------+-----------+--------------------------+-----------------+--------------------------------------+
| Aggregate_Type  | Entity_Type | Entity_Root | Entity_Id                            | Node_Type | Node_Name                | Node_MultiValue | Node_Content                         |
+-----------------+-------------+-------------+--------------------------------------+-----------+--------------------------+-----------------+--------------------------------------+
| ApplicationForm | Form        | 1           | D799728B-7973-4046-B60F-CB25D4EE385C | attribute | creationDate             | 0               | 2017-01-16                           |
+-----------------+-------------+-------------+--------------------------------------+-----------+--------------------------+-----------------+--------------------------------------+
| ApplicationForm | Form        | 1           | D799728B-7973-4046-B60F-CB25D4EE385C | attribute | product                  | 0               | Abc                                  |
+-----------------+-------------+-------------+--------------------------------------+-----------+--------------------------+-----------------+--------------------------------------+
| ApplicationForm | Form        | 1           | D799728B-7973-4046-B60F-CB25D4EE385C | relation  | r_PersonMain             | 0               | 4808f654-f480-412c-8dc5-d29c6c811602 |
+-----------------+-------------+-------------+--------------------------------------+-----------+--------------------------+-----------------+--------------------------------------+
| ApplicationForm | Form        | 1           | D799728B-7973-4046-B60F-CB25D4EE385C | relation  | r_PersonPayer            | 0               | a8e9eaf2-56a5-4f88-955b-19eb98f6e882 |
+-----------------+-------------+-------------+--------------------------------------+-----------+--------------------------+-----------------+--------------------------------------+
| ApplicationForm | Payment     | 1           | E197BF66-1E35-42A9-BDC0-0674E3A0F765 | attribute | totalAnnualPremium       | 0               | 328415.81                            |
+-----------------+-------------+-------------+--------------------------------------+-----------+--------------------------+-----------------+--------------------------------------+
| ApplicationForm | Payment     | 1           | E197BF66-1E35-42A9-BDC0-0674E3A0F765 | relation  | r_PaymentMethodRecurring | 0               | b8b3c652-b886-44aa-a75b-b2d3ecd6c064 |
+-----------------+-------------+-------------+--------------------------------------+-----------+--------------------------+-----------------+--------------------------------------+
| ApplicationForm | Payment     | 1           | E197BF66-1E35-42A9-BDC0-0674E3A0F765 | relation  | r_PaymentMethodFirst     | 0               | f3d91f99-ff6a-4888-a663-24e42ecc7342 |
+-----------------+-------------+-------------+--------------------------------------+-----------+--------------------------+-----------------+--------------------------------------+
| ApplicationForm | Payment     | 1           | E197BF66-1E35-42A9-BDC0-0674E3A0F765 | attribute | term                     | 0               | 01                                   |
+-----------------+-------------+-------------+--------------------------------------+-----------+--------------------------+-----------------+--------------------------------------+
| ApplicationForm | Person      | 0           | 4808F654-F480-412C-8DC5-D29C6C811602 | relation  | r_AddressWork            | 0               | cae83657-47c2-49bd-a588-7685271c4766 |
+-----------------+-------------+-------------+--------------------------------------+-----------+--------------------------+-----------------+--------------------------------------+
| ApplicationForm | Person      | 0           | 4808F654-F480-412C-8DC5-D29C6C811602 | attribute | idNumber                 | 0               | 1112223334447                        |
+-----------------+-------------+-------------+--------------------------------------+-----------+--------------------------+-----------------+--------------------------------------+
| ApplicationForm | Person      | 0           | 4808F654-F480-412C-8DC5-D29C6C811602 | relation  | r_SelectionItem          | 1               | ...                                  |
+-----------------+-------------+-------------+--------------------------------------+-----------+--------------------------+-----------------+--------------------------------------+
| ApplicationForm | Person      | 0           | 4808F654-F480-412C-8DC5-D29C6C811602 | relation  | r_Health                 | 0               | 07d08bd6-ec73-4710-9de4-23435cd2b088 |
+-----------------+-------------+-------------+--------------------------------------+-----------+--------------------------+-----------------+--------------------------------------+
| ApplicationForm | Person      | 0           | 4808F654-F480-412C-8DC5-D29C6C811602 | relation  | r_AddressCurrent         | 0               | 56d17bda-e332-497e-8e22-e7b7f09f996d |
+-----------------+-------------+-------------+--------------------------------------+-----------+--------------------------+-----------------+--------------------------------------+
| ApplicationForm | Person      | 0           | 4808F654-F480-412C-8DC5-D29C6C811602 | attribute | lastName                 | 0               | 1                                    |
+-----------------+-------------+-------------+--------------------------------------+-----------+--------------------------+-----------------+--------------------------------------+
| ApplicationForm | Person      | 0           | 4808F654-F480-412C-8DC5-D29C6C811602 | attribute | jobDescription1          | 0               | NULL                                 |
+-----------------+-------------+-------------+--------------------------------------+-----------+--------------------------+-----------------+--------------------------------------+
| ApplicationForm | Person      | 0           | 4808F654-F480-412C-8DC5-D29C6C811602 | relation  | r_Behavior               | 0               | 2db2c23a-37dd-4857-87b4-005aa87b2c2d |
+-----------------+-------------+-------------+--------------------------------------+-----------+--------------------------+-----------------+--------------------------------------+
| ApplicationForm | Person      | 0           | 4808F654-F480-412C-8DC5-D29C6C811602 | attribute | email                    | 0               | NULL                                 |
+-----------------+-------------+-------------+--------------------------------------+-----------+--------------------------+-----------------+--------------------------------------+
| ApplicationForm | Person      | 0           | 4808F654-F480-412C-8DC5-D29C6C811602 | relation  | r_AddressRegistered      | 0               | ce79a468-fb26-4996-91a8-82954d960855 |
+-----------------+-------------+-------------+--------------------------------------+-----------+--------------------------+-----------------+--------------------------------------+
| ApplicationForm | Person      | 0           | 4808F654-F480-412C-8DC5-D29C6C811602 | attribute | telephoneExtention1      | 0               | NULL                                 |
+-----------------+-------------+-------------+--------------------------------------+-----------+--------------------------+-----------------+--------------------------------------+
| ApplicationForm | Person      | 0           | 4808F654-F480-412C-8DC5-D29C6C811602 | relation  | r_Occupation1            | 0               | b7b69acc-2945-4f64-8ffd-4537849280f5 |
+-----------------+-------------+-------------+--------------------------------------+-----------+--------------------------+-----------------+--------------------------------------+

I can see, that the Form has a relation to person via person's id 4808F... Similar to payer vai payer's id. But I have no idea, what you are going to do from here (are these relations 1:1 or 1:n?)

Thank you for the answer, though i was looking for similar hierarchical format to be generated and not tabular. So that all elements referenced via 'ID' should come as children to parent element. — AAhad, Mar 08 '17 at 08:35
@AAhad That is the reason, why you should always post the **expected output**. I have no idea (how should I?) what you need... This is not possible to answer if you do not provide further details (read my final lines). Following a chain of linked entities will lead into *recursive CTE*. — Shnugo, Mar 08 '17 at 08:39
I was looking for ways to bring related entities under one parent element, thats what i was looking for. — AAhad, Mar 22 '17 at 06:33

SQL Server XQuery - iterate dynamic xml and return custom structure

2 Answers2