CODEWITHSUNDEEP
sql-serverxmlt-sqlxpathxquery
Fillister
Fillister

Reputation: 43

sql query a varchar(max) column to select an element based on the value of one of its children children's

I am trying to search xml data stored in a varchar(max) column.
Below is an example of one of the xml data strings found in the varchar column With this example I will have sql variable called @dsName which is to be matched against the node when its values matches @dsName and it is subordinate to either or nodes. The nodes in between or until you get to can vary.

   <business_process>
<ProcessDefinition name="dawns test">
    <StartState name="START" uniqueId="Node3304">
        <Transition name="Node4532" to="Node4532"/>
    </StartState>
    <EndState name="END4694" uniqueId="Node4694"/>
    <User name="Node4532" uniqueId="Node4532">
        <Description>test</Description>
        <Distribution config-type="field" type="CommonQueueDistribution">
            <Priority>0</Priority>
            <AutoCompleteJob>false</AutoCompleteJob>
            <GroupId>Admin</GroupId>
            <UseAttendance>false</UseAttendance>
            <UseShifts>false</UseShifts>
            <NotifyActors>false</NotifyActors>
        </Distribution>
        <DocFinityTask type="DocFinityTask">
            <description>read e-mail and approve or deny</description>
            <help/>
            <required>false</required>
            <redoable>false</redoable>
            <condition/>
            <properties>
                <undoable>true</undoable>
            </properties>
        </DocFinityTask>
        <DocFinityTask type="SimpleFormTask">
            <description>lob lookup</description>
            <help/>
            <required>false</required>
            <redoable>true</redoable>
            <condition/>
            <properties>
                <autoRun>true</autoRun>
                <form>
                    <title>lob</title>
                    <formElement>
                        <type>Combobox</type>
                        <variable>lob</variable>
                        <tooltip>lob lookup</tooltip>
                        <label>lob</label>
                        <required>false</required>
                        <prepopulateValues>
                            <datasourceName>lob lookup</datasourceName>
                        </prepopulateValues>
                        <userEnter>true</userEnter>
                        <dataType>STRING</dataType>
                    </formElement>
                </form>
            </properties>
        </DocFinityTask>
        <Transition name="Node128795" to="Node128795"/>
    </User>
    <Server name="Node128795" uniqueId="Node128795">
        <Description/>
        <Event type="node-enter">
            <Action type="SetProcessInstancePropertyAction" config-type="field">
                <description>Whatever</description>
                <propertyName>source</propertyName>
                <datasourceName>get datasource list</datasourceName>
            </Action>
        </Event>
        <Transition name="Node4694" to="END4694"/>
    </Server>
    <Server name="Node250" uniqueId="Node250">
      <Description />
      <Event type="node-enter">
        <Action type="SetProcessInstancePropertyAction" config-type="field">
          <description>Whatever</description>
          <propertyName>source</propertyName>
          <datasourceName>stump</datasourceName>
        </Action>
      </Event>
      <Transition name="Node4694" to="END4694" />
    </Server>
</ProcessDefinition>
<Layout>
    <annotations/>
    <nodes>
        <node name="START" uniqueId="Node3304" type="startNode" text="START" x="184.5" y="135.5" width="25" height="25"/>
        <node name="END4694" uniqueId="Node4694" type="endNode" text="END4694" x="588.5" y="137.5" width="25" height="25"/>
        <node name="Node4532" uniqueId="Node4532" type="userNode" text="Node4532" info="false" x="296" y="135" width="150" height="50"/>
        <node name="Node128795" uniqueId="Node128795" type="serverNode" text="Node128795" info="false" x="286" y="244" width="150" height="50"/>
    </nodes>
    <edges>
        <edge originNode="Node3304" targetNode="Node4532" text="" sketch="arrow"/>
        <edge originNode="Node4532" targetNode="Node128795" text="" sketch="arrow"/>
        <edge originNode="Node128795" targetNode="Node4694" text="" sketch="arrow"/>
    </edges>
</Layout>

Here is an example of the select I used when trying to load into a variable of an XML data type. The varchar(max) column name is XML

DECLARE @xml XML=
            (SELECT [XML] 
             FROM ProcessModels 
             WHERE [XML] LIKE '%<datasourceName>' + @dsName + '%' 
                    and [status] = 'ACTIVE')

The SQL select is that same if I load it into a table variable and the @dsName variable is already set with the string to search for.

In this example I want to find name of every Server node and/or User node when it has a node with the value of 'get datasource list'. The string 'get datasource list' already exists in the variable @dsName.

The following query gets me half way there.

select sn.value('@name', 'varchar(100)') AS ServerNodes
from @xml.nodes('/business_process/ProcessDefinition/Server')  AS ServerNodes(sn)

Now I need to figure out how to limit the Server.@name returned to just those where the child node //datasourceName value equals the sql:variable.

This worked:

SELECT ServerNode.value('@name','varchar(max)') AS ServerNode
FROM @xml.nodes('/business_process/ProcessDefinition') AS ProcessDefinition(pd)
OUTER APPLY pd.nodes('Server[Event//datasourceName=sql:variable("@searchVariable")]') AS The(ServerNode)
WHERE ServerNode.value('@name','varchar(max)') IS NOT NULL

SELECT UserNode.value('@name','varchar(max)') AS UserNode
FROM @xml.nodes('/business_process/ProcessDefinition') AS ProcessDefinition(pd)
OUTER APPLY pd.nodes('User[DocFinityTask//datasourceName=sql:variable("@searchVariable")]') AS The(UserNode)
WHERE UserNode.value('@name','varchar(max)') IS NOT NULL

Upvotes: 2

Views: 2531

Answers (3)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

As my first answer is already very crowded...

With this you'd get the User's and the Server's data out of the XML. If you set the @searchVariable to a non existent datasourceName-value, the User data is still there, but the Server data will be NULL. Try it out!

DECLARE @xml XML=
'<business_process>
  <ProcessDefinition name="dawns test">
    <StartState name="START" uniqueId="Node3304">
      <Transition name="Node4532" to="Node4532" />
    </StartState>
    <EndState name="END4694" uniqueId="Node4694" />
    <User name="Node4532" uniqueId="Node4532">
      <Description>test</Description>
      <Distribution config-type="field" type="CommonQueueDistribution">
        <Priority>0</Priority>
        <AutoCompleteJob>false</AutoCompleteJob>
        <GroupId>Admin</GroupId>
        <UseAttendance>false</UseAttendance>
        <UseShifts>false</UseShifts>
        <NotifyActors>false</NotifyActors>
      </Distribution>
      <DocFinityTask type="DocFinityTask">
        <description>read e-mail and approve or deny</description>
        <help />
        <required>false</required>
        <redoable>false</redoable>
        <condition />
        <properties>
          <undoable>true</undoable>
        </properties>
      </DocFinityTask>
      <DocFinityTask type="SimpleFormTask">
        <description>lob lookup</description>
        <help />
        <required>false</required>
        <redoable>true</redoable>
        <condition />
        <properties>
          <autoRun>true</autoRun>
          <form>
            <title>lob</title>
            <formElement>
              <type>Combobox</type>
              <variable>lob</variable>
              <tooltip>lob lookup</tooltip>
              <label>lob</label>
              <required>false</required>
              <prepopulateValues>
                <datasourceName>lob lookup</datasourceName>
              </prepopulateValues>
              <userEnter>true</userEnter>
              <dataType>STRING</dataType>
            </formElement>
          </form>
        </properties>
      </DocFinityTask>
      <Transition name="Node128795" to="Node128795" />
    </User>
    <Server name="Node128795" uniqueId="Node128795">
      <Description />
      <Event type="node-enter">
        <Action type="SetProcessInstancePropertyAction" config-type="field">
          <description>Whatever</description>
          <propertyName>source</propertyName>
          <datasourceName>get datasource list</datasourceName>
        </Action>
      </Event>
      <Transition name="Node4694" to="END4694" />
    </Server>
  </ProcessDefinition>
  <Layout>
    <annotations />
    <nodes>
      <node name="START" uniqueId="Node3304" type="startNode" text="START" x="184.5" y="135.5" width="25" height="25" />
      <node name="END4694" uniqueId="Node4694" type="endNode" text="END4694" x="588.5" y="137.5" width="25" height="25" />
      <node name="Node4532" uniqueId="Node4532" type="userNode" text="Node4532" info="false" x="296" y="135" width="150" height="50" />
      <node name="Node128795" uniqueId="Node128795" type="serverNode" text="Node128795" info="false" x="286" y="244" width="150" height="50" />
    </nodes>
    <edges>
      <edge originNode="Node3304" targetNode="Node4532" text="" sketch="arrow" />
      <edge originNode="Node4532" targetNode="Node128795" text="" sketch="arrow" />
      <edge originNode="Node128795" targetNode="Node4694" text="" sketch="arrow" />
    </edges>
  </Layout>
</business_process>';

DECLARE @searchVariable VARCHAR(100)='get datasource list';

SELECT ServerNode.value('@name','varchar(max)') AS ServerName
      ,ServerNode.value('@uniqueId','varchar(max)') AS ServerId
      ,pd.value('User[1]/@name','varchar(max)') AS UserName
      ,pd.value('User[1]/@uniqueId','varchar(max)') AS UserId
FROM @xml.nodes('/business_process/ProcessDefinition') AS ProcessDefinition(pd)
OUTER APPLY pd.nodes('Server[Event/Action/datasourceName=sql:variable("@searchVariable")]') AS The(ServerNode);

Upvotes: 2

TT.
TT.

Reputation: 16146

Yet another alternative. This doesn't use XML.exist but looks straight for User elements having a <source> element with the lookup variable. Then the path is reversed back to ancestor User and the name attribute is selected.

DECLARE @xml XML=
'<User name="First Node" uniqueId="1332">
    <Task type="Form">
        <properties>
            <form>
                <formElement>
                    <populateValues>
                        <source>lookup</source>
                    </populateValues>
                </formElement>
            </form>
        </properties>
    </Task>
</User>';

DECLARE @lookup NVARCHAR(128)='lookup';

SELECT
    n.v.value('../../../../../../@name','NVARCHAR(128)') AS name
FROM
    @xml.nodes('//User/Task/properties/form/formElement/populateValues/source[.=sql:variable("@lookup")]') AS n(v);

Apparantly XPath axes aren't fully supported (at least not on SQL Server 2012). If it were instead of ../../../../../../@name you could have written the easier ancestor::User/@name.

Upvotes: 1

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

Your friend is sql:variable(), there is also a sql:column() if your search value comes from a table's column.

According to your comment I mock up one table with an XML column of type varchar. The SELECT will first use CROSS APPLY to cast this to "real" XML, then .exist() is used to pick up the rows fullfilling your criteria and finally the value of /User/@name is returned.

If you change the lookup-variable to "another" you'll find the other XML, other strings will come back empty.

As examples of XPath I give you three queries all returning the same. This depends on your XML...

DECLARE @tbl TABLE(ID INT IDENTITY, YourXMLAsVarchar VARCHAR(MAX));
INSERT INTO @tbl VALUES 
('<User name="First Node" uniqueId="1332">
    <Task type="Form">
      <properties>
        <form>
          <formElement>
            <populateValues>
              <source>lookup</source>
            </populateValues>
          </formElement>
        </form>
      </properties>
    </Task>
   </User>')
,('<User name="First Node" uniqueId="1332">
    <Task type="Form">
      <properties>
        <form>
          <formElement>
            <populateValues>
              <source>another</source>
            </populateValues>
          </formElement>
        </form>
      </properties>
    </Task>
   </User>');


--Search for "lookup"
DECLARE @SearchingFor VARCHAR(100)='lookup';

--Search with full path
SELECT x.value('(/User/@name)[1]','varchar(max)')
FROM @tbl AS tbl
CROSS APPLY(SELECT CAST(YourXMLAsVarchar AS XML)) AS a(x)
WHERE x.exist('/User/Task/properties/form/formElement/populateValues/source[.=sql:variable("@SearchingFor")]')=1

--shorter, if there is not other "source" element this could be muddled up with...
SELECT x.value('(/User/@name)[1]','varchar(max)')
FROM @tbl AS tbl
CROSS APPLY(SELECT CAST(YourXMLAsVarchar AS XML)) AS a(x)
WHERE x.exist('//source[.=sql:variable("@SearchingFor")]')=1

--even shorter, if your lookup string won't be anywhere else an element's value
SELECT x.value('(/User/@name)[1]','varchar(max)')
FROM @tbl AS tbl
CROSS APPLY(SELECT CAST(YourXMLAsVarchar AS XML)) AS a(x)
WHERE x.exist('//*[.=sql:variable("@SearchingFor")]')=1

Upvotes: 2

Related Questions