您的位置: 首页  >  技术问答  >  SQL Server XQuery - 迭代动态xml并返回自定义结构

SQL Server XQuery - 迭代动态xml并返回自定义结构

分类: 技术问答 • 2022-07-03 18:36:23
问题描述：

我在SQL Server中有以下XML，并且想要遍历它以生成结构化消息。SQL Server XQuery - 迭代动态xml并返回自定义结构

<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>

顶部元素包含长列表entity标签。它每个都包含两个元素，attribute & relation。 attribute包含直接值，而relation包含对另一个entity标签的引用，该标签又包含attribute或relation。

为了挑选出entity表格（第一个标签），它需要迭代它及其所有引用，直到检索到所有引用的entities。

我可以检索第一个实体及其关系（参考实体），然后访问它的第二级参考实体，然后再次检查它的内部是否存在另一个“关系”标记，那么我也必须访问它。

问题是，这种方法不是动态的，不能自动检索所有引用的项目。问：我如何动态地访问所有引用的实体及其属性，直到没有引用的实体了。问：我还想根据每个属性或实体“名称”标签赋予我的自定义名称分配标签。例如creationDate，product。

这是我的查询。

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> 
    ')

答

不知道这是你在找什么，但我会用一个表值函数应用于动态解析几乎所有的XML结构为元素的层次结构，属性和值。结果可以通过范围键（R1/R2）或XPath进行处理和/或旋转。

原始来源是http://beyondrelational.com/modules/2/blogs/28/posts/10495/xquery-lab-58-select-from-xml.aspx只做了原来的一些调整。

表现是可敬的。您的示例XML在170 ms内处理完毕。

既然你是在2008年，我应该注意到你将不得不编辑CONCAT（）部分。

实施例：

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

返回

enter image description here

的UDF如果有意

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 
*/

答

你告诉我们，你想

产生structued消息

这将有助于显示预期的输出...

下面的代码将提取您的XML的所有数据在一个易于查询的派生表。

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查询

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;

结果（命名和（如果可能）类型）：

+-----------------+-------------+-------------+--------------------------------------+-----------+--------------------------+-----------------+--------------------------------------+ 
| 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 | 
+-----------------+-------------+-------------+--------------------------------------+-----------+--------------------------+-----------------+--------------------------------------+

我可以看到，该Form有通过关系人person's id 4808F...类似于付款人付款人的ID。但我不知道，你打算从这里做什么（这些关系是1：1还是1：n？）

+0

谢谢你的回答，虽然我在寻找类似的分层格式来生成，而不是表格。所以通过'ID'引用的所有元素都应该作为父元素的子元素来使用。 – AAhad

+0

@AAhad这就是为什么你应该总是发布**期望的输出**。我不知道（我应该怎么办？）你需要什么......如果你不提供更多的细节（阅读我的最后一行），这是不可能回答的。以下链接的实体将导致*递归CTE *。 – Shnugo

+0

我正在寻找方法将相关实体置于一个父元素之下，那就是我一直在寻找的东西。 – AAhad

相关推荐