包含嵌套元素的 SQL 表中的 XML

Question

我的任务是研究“黑匣子”流程的替代解决方案，该流程需要花费大量时间，但我们无法在其中进行更改或改进。

我想做的是从当前作为文本字段保存的表中的 XML 中提取信息(使用 CAST 对其进行转换)。有多行并且 XML 包含许多包含属性的嵌套元素。

为一行存储的 XML 示例如下:

<offerContext weightExpr="90">
  <filter label="Description of XML held here">
    <where displayFilter="Second description of XML held here" filterName="backGroundFilterFrm" id="13706004488">
      <condition boolOperator="AND" compositeKey="" dependkey="FK_Rcp_Brand" enabledIf="" expr="@BrandId = 1" internalId="-1548698833" />
      <condition boolOperator="AND" compositeKey="FK_Rcp_Brand" dependkey="" expr="FK_Rcp_Brand = '1'" internalId="1370600592" />
      <condition boolOperator="AND" compositeKey="" dependkey="" expr="proposition" internalId="1370600625" setOperator="EXISTS">
        <condition boolOperator="AND" compositeKey="" dependkey="" expr="@status = 3" internalId="1370600632" />
        <condition boolOperator="AND" compositeKey="" dependkey="" expr="[offer/@name] = 'Spend20get5Off'" internalId="1370600644" />
        <condition compositeKey="" dependkey="" expr="[offerSpace/@channel] = 0" internalId="1370600655" />
      </condition>
      <condition boolOperator="AND" compositeKey="" dependkey="" enabledIf="" expr="proposition" internalId="1372382776" setOperator="NOT EXISTS">
        <condition boolOperator="AND" compositeKey="" dependkey="" enabledIf="" expr="[offer/@name] = 'Spend20get5Off'" internalId="1372382779" />
        <condition boolOperator="AND" compositeKey="" dependkey="" enabledIf="" expr="@eventDate &gt;= DaysAgo(21)" internalId="1372382782" />
        <condition boolOperator="AND" compositeKey="" dependkey="" enabledIf="" expr="[offerSpace/@channel] = 0" internalId="1372382786" />
      </condition>
    </where>
    <humanCond>Query: Description of XML held here</humanCond>
  </filter>
  <extension useBuildPropositionsScript="false" />
</offerContext>

我需要从 offerContext 元素中提取 weightexpr。除此之外，我还需要来自每个条件元素的 booloperator、compositekey、dependkey、expr 和 internalId。我需要提取这些，以便子元素链接到它们的父元素，这是我遇到一些困难的地方。我有以下将两个元素拉到一行中的内容，但这之后需要进行一些操作(我没有问题，但想知道是否有更好的方法)，因为父条件元素重复了多次。

我目前的代码是:

;WITH contexts AS
    (
    SELECT a.iOfferId, a.iOfferContextId, a.mdata, CONVERT(xml,a.mdata) AS XMLmData
    FROM NmsOfferContext a
    )
SELECT 
    iOfferId 
    ,iOfferContextId
    ,p2.value('(@weightExpr)[1]', 'nvarchar(max)' ) AS dweight
    ,p2.value('(@boolOperator)[1]', 'nvarchar(max)' ) AS boolOperator2
    ,p2.value('(@dependKey)[1]', 'nvarchar(max)' )  AS dependKey2
    ,p2.value('(@expr)[1]', 'nvarchar(max)' )           AS expr2
    ,p2.value('(@setOperator)[1]', 'nvarchar(max)' )    AS setoperator2
    ,p2.value('(@internalId)[1]', 'nvarchar(max)' ) AS internalID2
    ,p3.value('(@boolOperator)[1]', 'nvarchar(max)' ) AS boolOperator3
    ,p3.value('(@dependKey)[1]', 'nvarchar(max)' )  AS dependKey3
    ,p3.value('(@expr)[1]', 'nvarchar(max)' )           AS expr3
    ,p3.value('(@setOperator)[1]', 'nvarchar(max)' )    AS setoperator3
    ,p3.value('(@internalId)[1]', 'nvarchar(max)' ) AS internalID3
FROM contexts 
CROSS APPLY XMLmData.nodes('/offerContext/*/*/condition') t(p2)
CROSS APPLY XMLmData.nodes('/offerContext/*/*/condition/condition') t2(p3)
ORDER BY iOfferContextId,
    p2.value('(@internalId)[1]', 'nvarchar(max)' ),
    p3.value('(@internalId)[1]', 'nvarchar(max)' )

最终，我需要根据 expr 值构造 SQL 查询，并为 WHERE 子句使用 booloperator，因此为什么我对元素有正确的顺序很重要(我相信我也可以通过 internalId 属性实现) ) 但保留父子关系是我需要一些帮助的地方。

如果我在正确的轨道上，任何帮助将不胜感激和确认将是伟大的。如果有什么需要更清楚的解释，请随时询问。

提前致谢。

Answer 1

您可以使用递归 CTE 分解 XML 以构建节点级别之间的关系。

declare @X xml = '
<offerContext weightExpr="90">
  <filter label="Description of XML held here">
    <where displayFilter="Second description of XML held here" filterName="backGroundFilterFrm" id="13706004488">
      <condition boolOperator="AND" compositeKey="" dependkey="FK_Rcp_Brand" enabledIf="" expr="@BrandId = 1" internalId="-1548698833" />
      <condition boolOperator="AND" compositeKey="FK_Rcp_Brand" dependkey="" expr="FK_Rcp_Brand = ''1''" internalId="1370600592" />
      <condition boolOperator="AND" compositeKey="" dependkey="" expr="proposition" internalId="1370600625" setOperator="EXISTS">
        <condition boolOperator="AND" compositeKey="" dependkey="" expr="@status = 3" internalId="1370600632" />
        <condition boolOperator="AND" compositeKey="" dependkey="" expr="[offer/@name] = ''Spend20get5Off''" internalId="1370600644" />
        <condition compositeKey="" dependkey="" expr="[offerSpace/@channel] = 0" internalId="1370600655" />
      </condition>
      <condition boolOperator="AND" compositeKey="" dependkey="" enabledIf="" expr="proposition" internalId="1372382776" setOperator="NOT EXISTS">
        <condition boolOperator="AND" compositeKey="" dependkey="" enabledIf="" expr="[offer/@name] = ''Spend20get5Off''" internalId="1372382779" />
        <condition boolOperator="AND" compositeKey="" dependkey="" enabledIf="" expr="@eventDate &gt;= DaysAgo(21)" internalId="1372382782" />
        <condition boolOperator="AND" compositeKey="" dependkey="" enabledIf="" expr="[offerSpace/@channel] = 0" internalId="1372382786" />
      </condition>
    </where>
    <humanCond>Query: Description of XML held here</humanCond>
  </filter>
  <extension useBuildPropositionsScript="false" />
</offerContext>';

with A as
(
  select T.X.value('@weightExpr', 'int') as weightExpr,
         T.X.query('filter/where/condition') as C,
         cast(null as int) as internalID,
         cast(null as int) as internalParentID,
         cast(null as varchar(10)) as boolOperator,
         cast(null as varchar(20)) as dependKey,
         cast(null as varchar(50)) as expr
  from @X.nodes('/offerContext') as T(X)
  union all
  select null,
         T.X.query('condition'),
         T.X.value('@internalId', 'int'),
         A.internalID,
         T.X.value('@boolOperator', 'varchar(10)'),
         T.X.value('@dependkey', 'varchar(100)'),
         T.X.value('@expr', 'varchar(100)')
  from A
    cross apply A.C.nodes('condition') as T(X)
)
select A.weightExpr,
       A.internalID,
       A.internalParentID,
       A.boolOperator,
       A.dependKey,
       A.expr
from A
order by A.internalID

结果:

weightExpr internalID  internalParentID boolOperator dependKey     expr
---------- ----------- ---------------- ------------ ------------- --------------------------------
90         NULL        NULL             NULL         NULL          NULL
NULL       -1548698833 NULL             AND          FK_Rcp_Brand  @BrandId = 1
NULL       1370600592  NULL             AND                        FK_Rcp_Brand = '1'
NULL       1370600625  NULL             AND                        proposition
NULL       1370600632  1370600625       AND                        @status = 3
NULL       1370600644  1370600625       AND                        [offer/@name] = 'Spend20get5Off'
NULL       1370600655  1370600625       NULL                       [offerSpace/@channel] = 0
NULL       1372382776  NULL             AND                        proposition
NULL       1372382779  1372382776       AND                        [offer/@name] = 'Spend20get5Off'
NULL       1372382782  1372382776       AND                        @eventDate >= DaysAgo(21)
NULL       1372382786  1372382776       AND                        [offerSpace/@channel] = 0

当源是表时的重写。

with A as
(
  select Y.offerID,
         T.X.value('@weightExpr', 'int') as weightExpr,
         T.X.query('filter/where/condition') as C,
         cast(null as int) as internalID,
         cast(null as int) as internalParentID,
         cast(null as varchar(10)) as boolOperator,
         cast(null as varchar(20)) as dependKey,
         cast(null as varchar(50)) as expr
  from dbo.YourTable as Y
    cross apply Y.X.nodes('/offerContext') as T(X)
  union all
  select A.offerID,
         null,
         T.X.query('condition'),
         T.X.value('@internalId', 'int'),
         A.internalID,
         T.X.value('@boolOperator', 'varchar(10)'),
         T.X.value('@dependkey', 'varchar(20)'),
         T.X.value('@expr', 'varchar(50)')
  from A
    cross apply A.C.nodes('condition') as T(X)
)
select A.offerID,
       A.weightExpr,
       A.internalID,
       A.internalParentID,
       A.boolOperator,
       A.dependKey,
       A.expr
from A
order by A.offerID,
         A.internalID