Getting multiple XML values from SQL

Question

I have a table called 'views' containing data pulled from the Microsoft Service Manager database. To simplify this question, I'll say that this table has 2 columns:

Views
----------
ViewId    (uniqueidentifier)
ConfigXML (xml)

A sample of the XML data is below:

<Data>
  <Adapters>
    <Adapter AdapterName="dataportal:EnterpriseManagementObjectProjectionAdapter">
      <AdapterAssembly>Microsoft.EnterpriseManagement.UI.SdkDataAccess</AdapterAssembly>
      <AdapterType>Microsoft.EnterpriseManagement.UI.SdkDataAccess.DataAdapters.EnterpriseManagementObjectProjectionAdapter</AdapterType>
    </Adapter>
    <Adapter AdapterName="viewframework://Adapters/AdvancedList">
      <AdapterAssembly>Microsoft.EnterpriseManagement.UI.ViewFramework</AdapterAssembly>
      <AdapterType>Microsoft.EnterpriseManagement.UI.ViewFramework.AdvancedListSupportAdapter</AdapterType>
    </Adapter>
    <Adapter AdapterName="omsdk://Adapters/Criteria">
      <AdapterAssembly>Microsoft.EnterpriseManagement.UI.SdkDataAccess</AdapterAssembly>
      <AdapterType>Microsoft.EnterpriseManagement.UI.SdkDataAccess.DataAdapters.SdkCriteriaAdapter</AdapterType>
    </Adapter>
  </Adapters>
  <ItemsSource>
    <AdvancedListSupportClass xmlns="clr-namespace:Microsoft.EnterpriseManagement.UI.ViewFramework;assembly=Microsoft.EnterpriseManagement.UI.ViewFramework" xmlns:av="http://schemas.microsoft.com/winfx/2006/xaml/presentation" xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml" xmlns:s="clr-namespace:System;assembly=mscorlib" DataTypeName="" AdapterName="viewframework://Adapters/AdvancedList" FullUpdateAdapter="dataportal:EnterpriseManagementObjectProjectionAdapter" DataSource="mom:ManagementGroup" FullUpdateFrequency="100" Streaming="true" IsRecurring="true" RecurrenceFrequency="{x:Static s:Int32.MaxValue}" UpdateItemsAdapter="dataportal:EnterpriseManagementObjectProjectionAdapter" AppendItemsAdapter="dataportal:EnterpriseManagementObjectProjectionAdapter" RemoveItemsAdapter="dataportal:EnterpriseManagementObjectProjectionAdapter">
      <AdvancedListSupportClass.Parameters>
        <QueryParameter Parameter="TypeProjectionId" Value="$MPElement[Name='IncidentManagement!System.WorkItem.Incident.ProjectionType']$" />
      </AdvancedListSupportClass.Parameters>
    </AdvancedListSupportClass>
  </ItemsSource>
  <Criteria>
    <QueryCriteria xmlns="http://tempuri.org/Criteria.xsd" Adapter="omsdk://Adapters/Criteria">
      <Criteria>
        <FreeformCriteria>
          <Freeform>
            <Criteria xmlns="http://Microsoft.EnterpriseManagement.Core.Criteria/">
              <Expression>
                <And>
                  <Expression>
                    <Or>
                      <Expression>
                        <SimpleExpression>
                          <ValueExpressionLeft>
                            <Property>$Context/Property[Type='CustomSystem_WorkItem_Incident_Library!System.WorkItem.Incident']/Status$</Property>
                          </ValueExpressionLeft>
                          <Operator>Equal</Operator>
                          <ValueExpressionRight>
                            <Value>{5e2d3932-ca6d-1515-7310-6f58584df73e}</Value>
                          </ValueExpressionRight>
                        </SimpleExpression>
                      </Expression>
                      <Expression>
                        <SimpleExpression>
                          <ValueExpressionLeft>
                            <Property>$Context/Property[Type='CustomSystem_WorkItem_Incident_Library!System.WorkItem.Incident']/Status$</Property>
                          </ValueExpressionLeft>
                          <Operator>Equal</Operator>
                          <ValueExpressionRight>
                            <Value>{b6679968-e84e-96fa-1fec-8cd4ab39c3de}</Value>
                          </ValueExpressionRight>
                        </SimpleExpression>
                      </Expression>
                    </Or>
                  </Expression>
                  <Expression>
                    <Or>
                      <Expression>
                        <SimpleExpression>
                          <ValueExpressionLeft>
                            <Property>$Context/Property[Type='CustomSystem_WorkItem_Incident_Library!System.WorkItem.Incident']/TierQueue$</Property>
                          </ValueExpressionLeft>
                          <Operator>Equal</Operator>
                          <ValueExpressionRight>
                            <Value>{e41fea6c-90fa-4c6d-48fb-6d90ef3e8348}</Value>
                          </ValueExpressionRight>
                        </SimpleExpression>
                      </Expression>
                      <Expression>
                        <SimpleExpression>
                          <ValueExpressionLeft>
                            <Property>$Context/Property[Type='CustomSystem_WorkItem_Incident_Library!System.WorkItem.Incident']/TierQueue$</Property>
                          </ValueExpressionLeft>
                          <Operator>Equal</Operator>
                          <ValueExpressionRight>
                            <Value>{bfe405d7-11f3-09cc-882f-709b5505849d}</Value>
                          </ValueExpressionRight>
                        </SimpleExpression>
                      </Expression>
...

In particular, I'm interested in the 'Expression' nodes. I'm trying to pull out the values from Expression/SimpleExpression/ValueExpressionLeft/Value but only where Expression/SimpleExpression/ValueExpressionRight/Property contains the text 'TierQueue'.

The amount of Expression Nodes that match this vary. Some of the ConfigXML values only have a single entry that matches, other ones have up to 10 entries. I need all that match.

The final table should look something like this:

ViewID                                  TierQueue
----------------                        -----------------
3CC97021-1C04-64BB-6391-00A48C07AB41    20ad0c6e-a41d-aab9-cc16-ae6e5efe45d8
08EA4E4C-ED4B-7E56-E257-04717A7289E8    e3d37f4a-3ccd-1abd-3180-9b439616ce43
1502A994-5A82-E6C9-E278-05569CC929C9    0fa5f999-4d19-3a72-a0c4-ff48db2bfdd8
1502A994-5A82-E6C9-E278-05569CC929C9    666d6236-0deb-4c8f-0922-9e44245d692e
F0301A91-C6B6-E332-7F82-06DC59352D93    dbff58a6-d7f0-4b15-33d4-e2b0edbd6fe8
F0301A91-C6B6-E332-7F82-06DC59352D93    f9ba3c86-d6f5-f91d-98aa-10db1f9d054d

What I've done so far

I'll admit, I have never worked with XML before and so what I've tried amounts to trying to piece stuff together from examples on the net.

This is about as far as I've got:

SELECT
ViewID,
n.p.value('(//*[local-name()="Value"]/text())[1]', 'nvarchar(max)') as TierQueueID
FROM views smv
CROSS APPLY smv.ConfigurationXML.nodes('(//*[local-name()="SimpleExpression"])') as n (p)

which unfortunately gives me the wrong results. It's just getting the first occurence of 'Value' in each XML document and then duplicating it a few times

score 0 · Accepted Answer · answered Jan 28 '20 at 13:06

For inspiration:

declare @x xml = N'
<Data>
  <Adapters>
    <Adapter AdapterName="dataportal:EnterpriseManagementObjectProjectionAdapter">
      <AdapterAssembly>Microsoft.EnterpriseManagement.UI.SdkDataAccess</AdapterAssembly>
      <AdapterType>Microsoft.EnterpriseManagement.UI.SdkDataAccess.DataAdapters.EnterpriseManagementObjectProjectionAdapter</AdapterType>
    </Adapter>
    <Adapter AdapterName="viewframework://Adapters/AdvancedList">
      <AdapterAssembly>Microsoft.EnterpriseManagement.UI.ViewFramework</AdapterAssembly>
      <AdapterType>Microsoft.EnterpriseManagement.UI.ViewFramework.AdvancedListSupportAdapter</AdapterType>
    </Adapter>
    <Adapter AdapterName="omsdk://Adapters/Criteria">
      <AdapterAssembly>Microsoft.EnterpriseManagement.UI.SdkDataAccess</AdapterAssembly>
      <AdapterType>Microsoft.EnterpriseManagement.UI.SdkDataAccess.DataAdapters.SdkCriteriaAdapter</AdapterType>
    </Adapter>
  </Adapters>
  <ItemsSource>
    <AdvancedListSupportClass xmlns="clr-namespace:Microsoft.EnterpriseManagement.UI.ViewFramework;assembly=Microsoft.EnterpriseManagement.UI.ViewFramework" xmlns:av="http://schemas.microsoft.com/winfx/2006/xaml/presentation" xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml" xmlns:s="clr-namespace:System;assembly=mscorlib" DataTypeName="" AdapterName="viewframework://Adapters/AdvancedList" FullUpdateAdapter="dataportal:EnterpriseManagementObjectProjectionAdapter" DataSource="mom:ManagementGroup" FullUpdateFrequency="100" Streaming="true" IsRecurring="true" RecurrenceFrequency="{x:Static s:Int32.MaxValue}" UpdateItemsAdapter="dataportal:EnterpriseManagementObjectProjectionAdapter" AppendItemsAdapter="dataportal:EnterpriseManagementObjectProjectionAdapter" RemoveItemsAdapter="dataportal:EnterpriseManagementObjectProjectionAdapter">
      <AdvancedListSupportClass.Parameters>
        <QueryParameter Parameter="TypeProjectionId" Value="$MPElement[Name=''IncidentManagement!System.WorkItem.Incident.ProjectionType'']$" />
      </AdvancedListSupportClass.Parameters>
    </AdvancedListSupportClass>
  </ItemsSource>

    <Criteria>
    <QueryCriteria xmlns="http://tempuri.org/Criteria.xsd" Adapter="omsdk://Adapters/Criteria">
      <Criteria>
        <FreeformCriteria>
          <Freeform>
            <Criteria xmlns="http://Microsoft.EnterpriseManagement.Core.Criteria/">
              <Expression>
                <And>
                  <Expression>
                    <Or>
                      <Expression>
                        <SimpleExpression>
                          <ValueExpressionLeft>
                            <Property>$Context/Property[Type=''CustomSystem_WorkItem_Incident_Library!System.WorkItem.Incident'']/Status$</Property>
                          </ValueExpressionLeft>
                          <Operator>Equal</Operator>
                          <ValueExpressionRight>
                            <Value>{5e2d3932-ca6d-1515-7310-6f58584df73e}</Value>
                          </ValueExpressionRight>
                        </SimpleExpression>
                      </Expression>
                      <Expression>
                        <SimpleExpression>
                          <ValueExpressionLeft>
                            <Property>$Context/Property[Type=''CustomSystem_WorkItem_Incident_Library!System.WorkItem.Incident'']/Status$</Property>
                          </ValueExpressionLeft>
                          <Operator>Equal</Operator>
                          <ValueExpressionRight>
                            <Value>{b6679968-e84e-96fa-1fec-8cd4ab39c3de}</Value>
                          </ValueExpressionRight>
                        </SimpleExpression>
                      </Expression>
                    </Or>
                  </Expression>
                  <Expression>
                    <Or>
                      <Expression>
                        <SimpleExpression>
                          <ValueExpressionLeft>
                            <Property>$Context/Property[Type=''CustomSystem_WorkItem_Incident_Library!System.WorkItem.Incident'']/TierQueue$</Property>
                          </ValueExpressionLeft>
                          <Operator>Equal</Operator>
                          <ValueExpressionRight>
                            <Value>{e41fea6c-90fa-4c6d-48fb-6d90ef3e8348}</Value>
                          </ValueExpressionRight>
                        </SimpleExpression>
                      </Expression>
                      <Expression>
                        <SimpleExpression>
                          <ValueExpressionLeft>
                            <Property>$Context/Property[Type=''CustomSystem_WorkItem_Incident_Library!System.WorkItem.Incident'']/TierQueue$</Property>
                          </ValueExpressionLeft>
                          <Operator>Equal</Operator>
                          <ValueExpressionRight>
                            <Value>{bfe405d7-11f3-09cc-882f-709b5505849d}</Value>
                          </ValueExpressionRight>
                        </SimpleExpression>
                      </Expression>
                    </Or>
                  </Expression>
                </And>
              </Expression>
            </Criteria>
            </Freeform>
            </FreeformCriteria>
           </Criteria>
        </QueryCriteria>
       </Criteria>
</Data>
';

declare @views table(viewid int, ConfigurationXML xml);

insert into @views(viewid, ConfigurationXML)
values (1, @x);
--------------------------------------------------------------

SELECT
ViewID,
n.p.value('local-name(.)', 'varchar(50)') as nodename,
n.p.value('declare default element namespace "http://Microsoft.EnterpriseManagement.Core.Criteria/"; (ValueExpressionRight/Value)[1]', 'nvarchar(max)') as TierQueueID
FROM @views smv
CROSS APPLY smv.ConfigurationXML.nodes('
declare default element namespace "http://Microsoft.EnterpriseManagement.Core.Criteria/";
//SimpleExpression[contains((ValueExpressionLeft/Property/text())[1], "TierQueue")]') as n (p);


WITH XMLNAMESPACES (DEFAULT 'http://Microsoft.EnterpriseManagement.Core.Criteria/')  
SELECT
ViewID,
n.p.value('local-name(.)', 'varchar(50)') as nodename,
n.p.value('(ValueExpressionRight/Value)[1]', 'nvarchar(max)') as TierQueueID
FROM @views smv
CROSS APPLY smv.ConfigurationXML.nodes('//SimpleExpression[ValueExpressionLeft/Property[contains(text()[1], "TierQueue")]]') as n (p);

They worked brilliantly - thankyou! Two questions though please... what does `local-name(.)` do exactly? And in this line: `n.p.value('(ValueExpressionRight/Value)[1]', 'nvarchar(max)')` why and how are you starting the path from the ValueExpressionRight node? All the examples I've come across so far implied that I had to enter the full path right back to the root. Apologies if these seem silly questions. — MB263, Jan 28 '20 at 14:31
local-name() returns the name of a node. local-name(.) = name of current node. The same goes for the cross apply you have used: //*[local-name()=xyz] ==any node , at any level, whose name is xyz (within a filter[] current . "dot" is implicit). The whole value(local-name(.)) is not needed in the select/query. — lptr, Jan 28 '20 at 15:09
The path "starts" from the "fragment" returned by the ConfigurationXML.nodes(//SimpleExpression) ==any SimpleExpression node (of the namespace..filtered/having ValueExpressionLeft with Property abc). Each fragment will be ...... You could get the fragment of each node() by putting in the select part ....nodename, n.p.query('.') as thexmlfragment, .......... — lptr, Jan 28 '20 at 15:13

Getting multiple XML values from SQL

1 Answers1