XML in SQL table containing nested elements

Question

I have been tasked with investigating an alternative solution to a 'black box' process that is taking a substantial amount of time, but one we cannot alter or improve upon within it.

What I am trying to do is extract information from XML within a table where it is currently held as a text field (using CAST to convert it). There are multiple rows and the XML contains a number of nested elements that contain attributes.

One example of the XML stored for one row is as follows:

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

What I need to extract is the weightexpr from the offerContext element. Alongside this I need the booloperator, compositekey, dependkey, expr and internalId from each of the condition elements. I need to extract these so that the child elements are linked to their parent elements and this is where I am having some difficulties. I have the following that pulls both elements into one row but this would take some manipulation afterwards (which I have no issue with but wanted to know is there a better way of doing this) as teh parent condition element is repeated multiple times.

The code I have so far is:

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

Ultimately I need to construct SQL queries based on the expr values and using the booloperator for the WHERE clauses, hence why it is important that I have the right order for the elements (which I believe I can also achieve with the internalId attribute) but the retaining the relationship between parent and child is where I need some assistance.

Any help would be appreciated and confirmation if I am on the right track would be great. If anything needs a clearer explanation then please feel free to ask.

Thanks in advance.

Answer 1

You can shred the XML using a recursive CTE to build the relation between levels of nodes.

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

Result:

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

A rewrite for when the source is a table.

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