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