CODEWITHSUNDEEP
sqlxmlxpath
zackm
zackm

Reputation: 69

SQL Server: XPath to iterate through multiple elements and return values

I am working with a vendor's database that has some XML data as such:

<ArrayOfAlertConditionShelve xmlns="http://schemas.datacontract.org/2004/07/VendorName.Alerting" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
  <AlertConditionShelve>
    <AndThenTimeInterval i:nil="true" />
    <ChainType>Trigger</ChainType>
    <ConditionTypeID>Core.Dynamic</ConditionTypeID>
    <Configuration>
      <AlertConditionDynamic xmlns="http://schemas.datacontract.org/2004/07/VendorName.Dynamic" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
        <ExprTree xmlns:a="http://schemas.datacontract.org/2004/07/VendorName.Alerting">
          <a:Child>
            <a:Expr>
              <a:Child>
                <a:Expr>
                  <a:Child />
                  <a:NodeType>Field</a:NodeType>
                  <a:Value>APM.ApplicationAlert|ApplicationName|Application.ApplicationAlert</a:Value>
                </a:Expr>
                <a:Expr>
                  <a:Child />
                  <a:NodeType>Constant</a:NodeType>
                  <a:Value>AppNameABC123</a:Value>
                </a:Expr>
              </a:Child>
              <a:NodeType>Operator</a:NodeType>
              <a:Value>=</a:Value>
            </a:Expr>
            <a:Expr>
              <a:Child>
                <a:Expr>
                  <a:Child />
                  <a:NodeType>Field</a:NodeType>
                  <a:Value>NodesCustomProperties|n_mute|Application.Node.CustomProperties</a:Value>
                </a:Expr>
                <a:Expr>
                  <a:Child />
                  <a:NodeType>Constant</a:NodeType>
                  <a:Value>false</a:Value>
                </a:Expr>
              </a:Child>
              <a:NodeType>Operator</a:NodeType>
              <a:Value>=</a:Value>
            </a:Expr>
            <a:Expr>
              <a:Child>
                <a:Expr>
                  <a:Child />
                  <a:NodeType>Field</a:NodeType>
                  <a:Value>APM.ApplicationCustomProperties|a_mute|Application.CustomProperties</a:Value>
                </a:Expr>
                <a:Expr>
                  <a:Child />
                  <a:NodeType>Constant</a:NodeType>
                  <a:Value>false</a:Value>
                </a:Expr>
              </a:Child>
              <a:NodeType>Operator</a:NodeType>
              <a:Value>=</a:Value>
            </a:Expr>
            <a:Expr>
              <a:Child>
                <a:Expr>
                  <a:Child />
                  <a:NodeType>Field</a:NodeType>
                  <a:Value>NodesCustomProperties|Prod_State|Application.Node.CustomProperties</a:Value>
                </a:Expr>
                <a:Expr>
                  <a:Child />
                  <a:NodeType>Constant</a:NodeType>
                  <a:Value>PROD</a:Value>
                </a:Expr>
              </a:Child>
              <a:NodeType>Operator</a:NodeType>
              <a:Value>=</a:Value>
            </a:Expr>
            <a:Expr>
              <a:Child>
                <a:Expr>
                  <a:Child>
                    <a:Expr>
                      <a:Child />
                      <a:NodeType>Field</a:NodeType>
                      <a:Value>APM.ComponentAlert|ComponentAvailability|ComponentAlert</a:Value>
                    </a:Expr>
                    <a:Expr>
                      <a:Child />
                      <a:NodeType>Constant</a:NodeType>
                      <a:Value>Down</a:Value>
                    </a:Expr>
                  </a:Child>
                  <a:NodeType>Operator</a:NodeType>
                  <a:Value>=</a:Value>
                </a:Expr>
                <a:Expr>
                  <a:Child>
                    <a:Expr>
                      <a:Child />
                      <a:NodeType>Field</a:NodeType>
                      <a:Value>APM.ComponentAlert|ComponentAvailability|ComponentAlert</a:Value>
                    </a:Expr>
                    <a:Expr>
                      <a:Child />
                      <a:NodeType>Constant</a:NodeType>
                      <a:Value>Critical</a:Value>
                    </a:Expr>
                  </a:Child>
                  <a:NodeType>Operator</a:NodeType>
                  <a:Value>=</a:Value>
                </a:Expr>
              </a:Child>
              <a:NodeType>Operator</a:NodeType>
              <a:Value>OR</a:Value>
            </a:Expr>
          </a:Child>
          <a:NodeType>Operator</a:NodeType>
          <a:Value>AND</a:Value>
        </ExprTree>
        <Scope xmlns:a="http://schemas.datacontract.org/2004/07/VendorName.Alerting" i:nil="true" />
        <TimeWindow i:nil="true" />
      </AlertConditionDynamic>
    </Configuration>
    <ConjunctionOperator>None</ConjunctionOperator>
    <IsInvertedMinCountThreshold>false</IsInvertedMinCountThreshold>
    <NetObjectsMinCountThreshold i:nil="true" />
    <ObjectType>APM: Component</ObjectType>
    <SustainTime>PT5M</SustainTime>
  </AlertConditionShelve>
</ArrayOfAlertConditionShelve>

The requirement I have is to return the value for the "a:value" element for each "a:NodeType" element. In addition, I need the string between the pipes { | | } in the "a:NodeType" element, though I can get that with a substring after the fact if it muddles up this solution too much.

I also need to see the values of the "ObjectType" and "SustainTime" elements. To add even more complexity, the "SustainTime" element doesn't always exist.

I've done a fair bit of searching through both StackOverflow and several general google searches digging through various websites and blogs, but unfortunately I just cannot seem to put the disparate pieces together.

For reference, this is the result I am looking for with the example above:

+-----------------------+----------+----------------+
|       NodeType        | Operator |     Value      |
+-----------------------+----------+----------------+
| ApplicationName       | =        | AppNameABC123  |
| n_mute                | =        | false          |
| a_mute                | =        | false          |
| ProdState             | =        | PROD           |
| ComponentAvailability | =        | Critical       |
| ComponentAvailability | =        | Down           |
| ObjectType            |          | APM: Component |
| SustainTime           |          | PT5M           |
+-----------------------+----------+----------------+

Couple of Notes:

This is and example of the associated SQL Query that would match this XML

SELECT * FROM Table
WHERE ( ApplicationName = 'AppNameABC123' AND n_mute = 0 AND a_mute = 0 AND ProdState = 'PROD')
AND ( ComponentAvailability = 'Critical' OR ComponentAvailability = 'Down' )

I appreciate any and all help or guidance! I am pretty solid with SQL Server queries as a rule, but diving into XML has been humbling to say the least.

EDIT

I've made a bit of progress after stumbling upon an article that made a little more sense to my brain:

https://www.red-gate.com/simple-talk/sql/learn-sql-server/the-xml-methods-in-sql-server/ 

-- exist() Method
-- DbObject.exist('XQuery')
    -- 1 if the XQuery expression returns a nonempty result
    -- 0 if the XQuery expression returns an empty result.
    -- NULL value if the XML data type instance is null.
SELECT 
v.AlertID
,v.alertTriggerQuery.value(
    '(/*:Expr/*:NodeType) [1]','varchar(max)'
) 'alertTriggerQueryNodeTypeValue'
,v.alertTriggerQuery.value(
    '(/*:Expr/*:Value) [1]','varchar(max)'
) 'alertTriggerQueryValue'
,v.objectType.value(
    '(/*:ObjectType) [1]', 'varchar(100)'
) 'alertTriggerValue'
,v.sustainTime.value(
    '(/*:SustainTime) [1]', 'varchar(10)'
) 'sustainTimeValue'
,v.sustainTime.value('concat("TRIGGER DELAY: ",
    (/*:SustainTime) [1])', 'varchar(100)'
) 'sustainTimeValueConcat'
FROM
    (SELECT
    q.AlertID
    ,q.triggerXML.query('
        /*:ArrayOfAlertConditionShelve/*:AlertConditionShelve/*:Configuration/*:AlertConditionDynamic/*:ExprTree/*:Child/*:Expr/*:Child/*:Expr
    ') 'alertTriggerQuery'
    ,q.triggerXML.query('
        /*:ArrayOfAlertConditionShelve/*:AlertConditionShelve/*:ObjectType
    ') 'objectType'
    ,q.triggerXML.query('
        /*:ArrayOfAlertConditionShelve/*:AlertConditionShelve/*:SustainTime
    ') 'sustainTime'
    FROM (
        SELECT
            AlertID
            ,CAST(REPLACE(REPLACE([Trigger],'&lt;','<'),'&gt;','>') AS XML) 'triggerXML'
        FROM AlertConfigurations
        WHERE AlertID IN ( 280, 3052 )
    ) q
    WHERE q.triggerXML.exist('/*:ArrayOfAlertConditionShelve/*:AlertConditionShelve[*:SustainTime="PT5M"]') = 1
) v;
GO

Using a combination of the query(), value(), and exist() methods, I have been able to drill into the XML and get the following data out:

+---------+--------------------------------+-------------------------------------------------------------------+-------------------+------------------+------------------------+
| AlertID | alertTriggerQueryNodeTypeValue |                      alertTriggerQueryValue                       | alertTriggerValue | sustainTimeValue | sustainTimeValueConcat |
+---------+--------------------------------+-------------------------------------------------------------------+-------------------+------------------+------------------------+
|     280 | Field                          | APM.ApplicationAlert|ApplicationName|Application.ApplicationAlert | APM:Component     | PT5M             | TRIGGER DELAY: PT5M    |
+---------+--------------------------------+-------------------------------------------------------------------+-------------------+------------------+------------------------+

So, I think the next step is to figure out how to get the detailed information out of the 'alertTriggerQuery' XML in my subselect statement; which looks like this: 

<p1:Expr xmlns:p1="http://schemas.datacontract.org/2004/07/VENDORNAME.Alerting">
  <p1:Child />
  <p1:NodeType>Field</p1:NodeType>
  <p1:Value>APM.ApplicationAlert|ApplicationName|Application.ApplicationAlert</p1:Value>
</p1:Expr>
<p2:Expr xmlns:p2="http://schemas.datacontract.org/2004/07/VENDORNAME.Alerting">
  <p2:Child />
  <p2:NodeType>Constant</p2:NodeType>
  <p2:Value>WINWATCHER_WEB_02_URL</p2:Value>
</p2:Expr>
<p3:Expr xmlns:p3="http://schemas.datacontract.org/2004/07/VENDORNAME.Alerting">
  <p3:Child />
  <p3:NodeType>Field</p3:NodeType>
  <p3:Value>NodesCustomProperties|n_mute|Application.Node.CustomProperties</p3:Value>
</p3:Expr>
<p4:Expr xmlns:p4="http://schemas.datacontract.org/2004/07/VENDORNAME.Alerting">
  <p4:Child />
  <p4:NodeType>Constant</p4:NodeType>
  <p4:Value>false</p4:Value>
</p4:Expr>
<p5:Expr xmlns:p5="http://schemas.datacontract.org/2004/07/VENDORNAME.Alerting">
  <p5:Child />
  <p5:NodeType>Field</p5:NodeType>
  <p5:Value>APM.ApplicationCustomProperties|a_mute|Application.CustomProperties</p5:Value>
</p5:Expr>
<p6:Expr xmlns:p6="http://schemas.datacontract.org/2004/07/VENDORNAME.Alerting">
  <p6:Child />
  <p6:NodeType>Constant</p6:NodeType>
  <p6:Value>false</p6:Value>
</p6:Expr>
<p7:Expr xmlns:p7="http://schemas.datacontract.org/2004/07/VENDORNAME.Alerting">
  <p7:Child />
  <p7:NodeType>Field</p7:NodeType>
  <p7:Value>NodesCustomProperties|Prod_State|Application.Node.CustomProperties</p7:Value>
</p7:Expr>
<p8:Expr xmlns:p8="http://schemas.datacontract.org/2004/07/VENDORNAME.Alerting">
  <p8:Child />
  <p8:NodeType>Constant</p8:NodeType>
  <p8:Value>PROD</p8:Value>
</p8:Expr>
<p9:Expr xmlns:p9="http://schemas.datacontract.org/2004/07/VENDORNAME.Alerting">
  <p9:Child>
    <p9:Expr>
      <p9:Child />
      <p9:NodeType>Field</p9:NodeType>
      <p9:Value>APM.ComponentAlert|ComponentAvailability|ComponentAlert</p9:Value>
    </p9:Expr>
    <p9:Expr>
      <p9:Child />
      <p9:NodeType>Constant</p9:NodeType>
      <p9:Value>Down</p9:Value>
    </p9:Expr>
  </p9:Child>
  <p9:NodeType>Operator</p9:NodeType>
  <p9:Value>=</p9:Value>
</p9:Expr>
<pA:Expr xmlns:pA="http://schemas.datacontract.org/2004/07/VENDORNAME.Alerting">
  <pA:Child>
    <pA:Expr>
      <pA:Child />
      <pA:NodeType>Field</pA:NodeType>
      <pA:Value>APM.ComponentAlert|ComponentAvailability|ComponentAlert</pA:Value>
    </pA:Expr>
    <pA:Expr>
      <pA:Child />
      <pA:NodeType>Constant</pA:NodeType>
      <pA:Value>Critical</pA:Value>
    </pA:Expr>
  </pA:Child>
  <pA:NodeType>Operator</pA:NodeType>
  <pA:Value>=</pA:Value>
</pA:Expr>

The current hurdle is figuring out how to return a single result table that iterates every parent node's values for "NodeType" and "Value"

i.e.: 

+---------+------+----------+-------------------------------------------------------------------+-------------------+------------------+------------------------+
| AlertID | Node | NodeType |                               Value                               | alertTriggerValue | sustainTimeValue | sustainTimeValueConcat |
+---------+------+----------+-------------------------------------------------------------------+-------------------+------------------+------------------------+
|     280 | p1   | Field    | APM.ApplicationAlert|ApplicationName|Application.ApplicationAlert | APM:Component     | PT5M             | TRIGGER DELAY: PT5M    |
|     280 | p2   | Constant | WINWATCHER_WEB_02_URL                                             | APM:Component     | PT5M             | TRIGGER DELAY: PT5M    |
|     280 | ...  | ...      | ...                                                               | ...               | ...              | ...                    |
|     280 | p9   | Field    | APM.ComponentAlert|ComponentAvailability|ComponentAlert           | APM:Component     | PT5M             | TRIGGER DELAY: PT5M    |
|     280 | p9   | Constant | Down                                                              | APM:Component     | PT5M             | TRIGGER DELAY: PT5M    |
|     280 | p9   | Operator | =                                                                 | APM:Component     | PT5M             | TRIGGER DELAY: PT5M    |
|     280 | pA   | Field    | APM.ComponentAlert|ComponentAvailability|ComponentAlert           | APM:Component     | PT5M             | TRIGGER DELAY: PT5M    |
|     280 | pA   | Constant | Critical                                                          | APM:Component     | PT5M             | TRIGGER DELAY: PT5M    |
|     280 | pA   | Operator | =                                                                 | APM:Component     | PT5M             | TRIGGER DELAY: PT5M    |
+---------+------+----------+-------------------------------------------------------------------+-------------------+------------------+------------------------+

I believe my current limitation is in my "v.alertTriggerQuery.value()" statement where I have to call out the node position "[1]". In PowerShell, I would look at doing something similar to a Foreach loop after placing all of the nodes into an array; but I am not sure how to translate that logic into SQL.

Upvotes: 3

Views: 1752

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67321

There is no easy going I'm afraid...

DECLARE @yourTable TABLE(ID INT IDENTITY, TheXml XML);
INSERT INTO @yourTable VALUES
(N'<ArrayOfAlertConditionShelve xmlns="http://schemas.datacontract.org/2004/07/VendorName.Alerting" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
  <AlertConditionShelve>
    <AndThenTimeInterval i:nil="true" />
    <ChainType>Trigger</ChainType>
    <ConditionTypeID>Core.Dynamic</ConditionTypeID>
    <Configuration>
      <AlertConditionDynamic xmlns="http://schemas.datacontract.org/2004/07/VendorName.Dynamic" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
        <ExprTree xmlns:a="http://schemas.datacontract.org/2004/07/VendorName.Alerting">
          <a:Child>
            <a:Expr>
              <a:Child>
                <a:Expr>
                  <a:Child />
                  <a:NodeType>Field</a:NodeType>
                  <a:Value>APM.ApplicationAlert|ApplicationName|Application.ApplicationAlert</a:Value>
                </a:Expr>
                <a:Expr>
                  <a:Child />
                  <a:NodeType>Constant</a:NodeType>
                  <a:Value>AppNameABC123</a:Value>
                </a:Expr>
              </a:Child>
              <a:NodeType>Operator</a:NodeType>
              <a:Value>=</a:Value>
            </a:Expr>
            <a:Expr>
              <a:Child>
                <a:Expr>
                  <a:Child />
                  <a:NodeType>Field</a:NodeType>
                  <a:Value>NodesCustomProperties|n_mute|Application.Node.CustomProperties</a:Value>
                </a:Expr>
                <a:Expr>
                  <a:Child />
                  <a:NodeType>Constant</a:NodeType>
                  <a:Value>false</a:Value>
                </a:Expr>
              </a:Child>
              <a:NodeType>Operator</a:NodeType>
              <a:Value>=</a:Value>
            </a:Expr>
            <a:Expr>
              <a:Child>
                <a:Expr>
                  <a:Child />
                  <a:NodeType>Field</a:NodeType>
                  <a:Value>APM.ApplicationCustomProperties|a_mute|Application.CustomProperties</a:Value>
                </a:Expr>
                <a:Expr>
                  <a:Child />
                  <a:NodeType>Constant</a:NodeType>
                  <a:Value>false</a:Value>
                </a:Expr>
              </a:Child>
              <a:NodeType>Operator</a:NodeType>
              <a:Value>=</a:Value>
            </a:Expr>
            <a:Expr>
              <a:Child>
                <a:Expr>
                  <a:Child />
                  <a:NodeType>Field</a:NodeType>
                  <a:Value>NodesCustomProperties|Prod_State|Application.Node.CustomProperties</a:Value>
                </a:Expr>
                <a:Expr>
                  <a:Child />
                  <a:NodeType>Constant</a:NodeType>
                  <a:Value>PROD</a:Value>
                </a:Expr>
              </a:Child>
              <a:NodeType>Operator</a:NodeType>
              <a:Value>=</a:Value>
            </a:Expr>
            <a:Expr>
              <a:Child>
                <a:Expr>
                  <a:Child>
                    <a:Expr>
                      <a:Child />
                      <a:NodeType>Field</a:NodeType>
                      <a:Value>APM.ComponentAlert|ComponentAvailability|ComponentAlert</a:Value>
                    </a:Expr>
                    <a:Expr>
                      <a:Child />
                      <a:NodeType>Constant</a:NodeType>
                      <a:Value>Down</a:Value>
                    </a:Expr>
                  </a:Child>
                  <a:NodeType>Operator</a:NodeType>
                  <a:Value>=</a:Value>
                </a:Expr>
                <a:Expr>
                  <a:Child>
                    <a:Expr>
                      <a:Child />
                      <a:NodeType>Field</a:NodeType>
                      <a:Value>APM.ComponentAlert|ComponentAvailability|ComponentAlert</a:Value>
                    </a:Expr>
                    <a:Expr>
                      <a:Child />
                      <a:NodeType>Constant</a:NodeType>
                      <a:Value>Critical</a:Value>
                    </a:Expr>
                  </a:Child>
                  <a:NodeType>Operator</a:NodeType>
                  <a:Value>=</a:Value>
                </a:Expr>
              </a:Child>
              <a:NodeType>Operator</a:NodeType>
              <a:Value>OR</a:Value>
            </a:Expr>
          </a:Child>
          <a:NodeType>Operator</a:NodeType>
          <a:Value>AND</a:Value>
        </ExprTree>
        <Scope xmlns:a="http://schemas.datacontract.org/2004/07/VendorName.Alerting" i:nil="true" />
        <TimeWindow i:nil="true" />
      </AlertConditionDynamic>
    </Configuration>
    <ConjunctionOperator>None</ConjunctionOperator>
    <IsInvertedMinCountThreshold>false</IsInvertedMinCountThreshold>
    <NetObjectsMinCountThreshold i:nil="true" />
    <ObjectType>APM: Component</ObjectType>
    <SustainTime>PT5M</SustainTime>
  </AlertConditionShelve>
</ArrayOfAlertConditionShelve>');

--Some general hints:

--Easy-Cheesy: The two meta-data values
SELECT t.TheXml.value(N'(//*:ObjectType/text())[1]',N'nvarchar(max)') AS ObjectType
      ,t.TheXml.value(N'(//*:SustainTime/text())[1]',N'nvarchar(max)') AS SustainTime
FROM @yourTable AS t;

--Specified default namespace, full XPath
WITH XMLNAMESPACES(DEFAULT 'http://schemas.datacontract.org/2004/07/VendorName.Alerting')
SELECT t.TheXml.value(N'(/ArrayOfAlertConditionShelve/AlertConditionShelve/ObjectType/text())[1]',N'nvarchar(max)') AS ObjectType
      ,t.TheXml.value(N'(/ArrayOfAlertConditionShelve/AlertConditionShelve/SustainTime/text())[1]',N'nvarchar(max)') AS SustainTime
FROM @yourTable AS t

--You can use the "deep search" with // to find all elements, but this won't really help:

--Eays-Cheesy: All NodeTypes with their Values but not in the correct order and nesting
SELECT AnyExprWithNodeType.value(N'(*:NodeType/text())[1]',N'nvarchar(max)') AS NodeType
      ,AnyExprWithNodeType.value(N'(*:Value/text())[1]',N'nvarchar(max)') AS NodeValue
FROM @yourTable AS t
CROSS APPLY t.TheXml.nodes(N'//*:Expr[*:NodeType]') AS A(AnyExprWithNodeType);

--Try to solve this (no deeper nesting) --This works until "OR", the rest is deeper nested.

WITH XMLNAMESPACES(DEFAULT 'http://schemas.datacontract.org/2004/07/VendorName.Alerting'
                  ,'http://schemas.datacontract.org/2004/07/VendorName.Alerting' AS a
                  ,'http://schemas.datacontract.org/2004/07/VendorName.Dynamic' AS innerDflt)
SELECT FirstLvlExpr.value(N'(a:Child//a:Expr[a:NodeType/text()="Field"]/a:Value/text())[1]','nvarchar(max)') AS NodeType
      ,FirstLvlExpr.value(N'(a:Value/text())[1]','nvarchar(max)') AS Operator  
      ,FirstLvlExpr.value(N'(a:Child//a:Expr[a:NodeType/text()="Constant"]/a:Value/text())[1]','nvarchar(max)') AS Value 
FROM @yourTable AS t
CROSS APPLY t.TheXml.nodes(N'/ArrayOfAlertConditionShelve
                              /AlertConditionShelve
                              /Configuration
                              /innerDflt:AlertConditionDynamic
                              /innerDflt:ExprTree
                              /a:Child
                              /a:Expr') AS A(FirstLvlExpr);

I assume, that such a tree can be very complex and hierarchically nested. You might use a recursive CTE or some kind of repeated CROSS APPLY with .nodes() to dive deeper and deeper.

Hope this helps...

UPDATE

The following statement will start with all elements <NodeType> where the text() is Field. The call to .nodes() creates a derived table from all fitting nodes, wherever they are.

The next step uses backward-navigation (..) to climb up the parents and find the related value and operator. Try it out:

WITH XMLNAMESPACES(DEFAULT 'http://schemas.datacontract.org/2004/07/VendorName.Alerting'
                  ,'http://schemas.datacontract.org/2004/07/VendorName.Alerting' AS a
                  ,'http://schemas.datacontract.org/2004/07/VendorName.Dynamic' AS innerDflt)
SELECT B.NodeTypeSplitted
      ,B.NodeTypeSplitted.value(N'/*:x[2]/text()[1]',N'nvarchar(max)') AS SecondPartOfNodeType
      ,Fields.value(N'(../../../a:Value/text())[1]','nvarchar(max)') AS Operator 
      ,Fields.value(N'(../../a:Expr[a:NodeType[text()="Constant"]]/a:Value/text())[1]','nvarchar(max)') AS NodeType 
FROM @yourTable AS t
CROSS APPLY t.TheXml.nodes(N'//a:NodeType[text()="Field"]') AS A(Fields)
CROSS APPLY (SELECT CAST('<x>' + REPLACE((SELECT Fields.value(N'(../a:Value/text())[1]','nvarchar(max)') AS [*] FOR XML PATH('')),'|','</x><x>') + '</x>' AS XML) AS NodeTypeSplitted) AS B;

The result

SecondPartOfNodeType    Operator    NodeType
ApplicationName         =           AppNameABC123
n_mute                  =           false
a_mute                  =           false
Prod_State              =           PROD
ComponentAvailability   =           Down
ComponentAvailability   =           Critical

The splitting of the piped values is done with an XML trick.

Upvotes: 4

Related Questions