Filtering in nodes() XQuery by Referencing Current Context Node

Question

Is it possible to reference the current context node in a nodes()'s XQuery statement?

Example

Suppose I have XML data sorted in a table/table variable where one section of each document references an element in another section by an attribute. (In the below, person and position are associated by ID.)

DECLARE @Data TABLE (
    ID INT NOT NULL PRIMARY KEY,
    XmlData XML NOT NULL
)

INSERT INTO @Data
    VALUES (1, '<Root>
    <People>
        <Person id="1">Frank</Person>
        <Person id="2">Joe</Person>
    </People>
    <Positions>
        <Position assignedToPerson="1">Engineer</Position>
        <Position assignedToPerson="2">Manager</Position>
    </Positions>
    </Root>'),
    (2, '<Root>
    <People>
        <Person id="5">Bob</Person>
        <Person id="6">Sam</Person>
    </People>
    <Positions>
        <Position assignedToPerson="6">Mechanic</Position>
        <Position assignedToPerson="5">Accountant</Position>
    </Positions>
</Root>')

A resultset of person-position pairings can be produced like this:

SELECT 
   PersonID = person.value('@id', 'NVARCHAR(50)'),
   Name = person.value('.', 'NVARCHAR(50)'),
   Position = position.value('.', 'NVARCHAR(50)')
FROM @Data
    CROSS APPLY XmlData.nodes('/Root/People/Person') People(person)
    CROSS APPLY person.nodes('/Root/Positions/Position') Positions(position)
WHERE person.value('@id', 'NVARCHAR(50)')= position.value('@assignedToPerson[1]','NVARCHAR(50)')

+----------+-------+------------+
| PersonID | Name  |  Position  |
+----------+-------+------------+
|        1 | Frank | Engineer   |
|        2 | Joe   | Manager    |
|        5 | Bob   | Accountant |
|        6 | Sam   | Mechanic   |
+----------+-------+------------+

Problem

The second CROSS APPLY breaks out each position defined in the relevant XML document into its own row each time it's called. The result is that each person in a document is paired with each position defined in the document. Filtering the resultset down to related person-position pairs occurs in the WHERE clause.

Goal

I'd like to eliminate the matching all people to every position by referencing the context node of People(person) in the second XQuery--something like this:

SELECT 
   PersonID = person.value('@id', 'NVARCHAR(50)'),
   Name = person.value('.', 'NVARCHAR(50)'),
   Position = position.value('.', 'NVARCHAR(50)')
FROM @Data
    CROSS APPLY XmlData.nodes('/Root/People/Person') People(person)
    CROSS APPLY person.nodes('/Root/Positions/Position[@assignedToPerson={{**reference to @ID of context node**}}]') Positions(position)

Can I reference the first nodes() context node in the second CROSS APPLY's XQuery?

(Using a JOIN-based approach doesn't work as the data for the above comes from a table, not an XML variable.)

Bogdan Sahlean · Accepted Answer · 2014-08-03T07:20:42.237

Basically, you want to JOIN persons and positions.

(1) This can be done easily if those data about people and positions where stored in tables. This means, also, that I would store this information in separate columns than a single XML column.

(2) If, for some reasons, you can't do this then you could redesign XML thus:

<Root>
  <People>
    <Person id="1" name="Frank" position="Engineer" />
    <Person id="2" name="Joe" position="Manager" />
  </People>
</Root>

(Example:

UPDATE  x
SET     XmlData = NewXmlData
FROM (
    SELECT  d.ID, d.XmlData,  d.XmlData.query('
        <Root>
        <People>
        {for $per in (/Root/People/Person)
            for $pos in (/Root/Positions/Position[@assignedToPerson = $per/@id])
                return  
                    <Person id="{$per/@id}" name="{$per/text()}" position="{$pos/text()}"/>}
        </People>
        </Root>
        ') AS NewXmlData
    FROM    @Data d
) x 

SELECT  d.ID,
        x.XmlPerson.value('(@name)[1]', 'NVARCHAR(50)') AS name,
        x.XmlPerson.value('(@position)[1]', 'NVARCHAR(50)') AS position
FROM    @Data d
CROSS APPLY d.XmlData.nodes('/Root/People/Person') x(XmlPerson)

)

or thus

<Root>
  <People>
    <Person id="1" name="Frank">
      <Positions>
        <Position>Engineer</Position>
      </Positions>
    </Person>
    <Person id="2" name="Joe">
      <Positions>
        <Position>Manager</Position>
      </Positions>
    </Person>
  </People>
</Root>

(incomplete example:

SELECT  d.XmlData.query('
<Root>
    <People>
    {for $per in (/Root/People/Person)
        return
            <Person id="{$per/@id}" name="{$per/text()}">
                <Positions>
                {for $pos in (/Root/Positions/Position[@assignedToPerson = $per/@id])
                    return <Position>{string($pos/text()[1])}</Position>} 
                </Positions>
            </Person> 
    }
    </People>
</Root>') AS NewXmlData
FROM    @Data d

)

(3) If, for some reasons, you can't do this and if you want to find a better solution (from performance point of view) then you could use one of following solutions:

PRINT 'Solution #1'
SELECT  Person.Person_id, 
        Person.Person_name,
        Position_name = Person.XmlData.value('(/Root/Positions/Position[@assignedToPerson = sql:column("Person_id")]/text())[1]', 'NVARCHAR(50)')
FROM (
    SELECT  Person_id = x.XmlPerson.value('(@id)[1]', 'INT'),
            Person_name = x.XmlPerson.value('(text())[1]', 'NVARCHAR(50)'),
            d.XmlData
    FROM    @Data d
    CROSS APPLY d.XmlData.nodes('/Root/People/Person') x(XmlPerson)
) Person 

PRINT 'Solution #2'
SELECT  Person.Person_id, 
        Person.Person_name,
        Position.Position_name
FROM (
    SELECT  Person_id = x.XmlPerson.value('(@id)[1]', 'INT'),
            Person_name = x.XmlPerson.value('(text())[1]', 'NVARCHAR(50)'),
            d.XmlData
    FROM    @Data d
    CROSS APPLY d.XmlData.nodes('/Root/People/Person') x(XmlPerson)
) Person INNER /*HASH*/ JOIN (
    SELECT  Position_assignedToPerson = x.XmlPerson.value('(@assignedToPerson)[1]', 'INT'),
            Position_name = x.XmlPerson.value('(text())[1]', 'NVARCHAR(50)')
    FROM    @Data d
    CROSS APPLY d.XmlData.nodes('/Root/Positions/Position') x(XmlPerson)
) Position ON Person.Person_id = Position.Position_assignedToPerson

PRINT 'Solution #3'
SELECT  Person.Person_id, 
        Person.Person_name,
        Position_name = y.XmlPosition.value('(text())[1]', 'NVARCHAR(50)')
FROM (
    SELECT  Person_id = x.XmlPerson.value('(@id)[1]', 'INT'),
            Person_name = x.XmlPerson.value('(text())[1]', 'NVARCHAR(50)'),
            d.XmlData
    FROM    @Data d
    CROSS APPLY d.XmlData.nodes('/Root/People/Person') x(XmlPerson)
) Person
CROSS APPLY Person.XmlData.nodes('/Root/Positions/Position[@assignedToPerson = sql:column("Person_id")]') y(XmlPosition);

Note: If this isn't one time task then I will go with (1).

Note #2: The only solution (more or less) to your problem "Is it possible to reference the current context node in a nodes()'s XQuery statement?" is example from (2).

Filtering in nodes() XQuery by Referencing Current Context Node

Example

Problem

Goal

1 Answers1