value() and nodes() method in SQL server

Question

Here is my code

DECLARE @dataModel XML
SET @dataModel =
   '<object name = "cardApplication" type = "businessObject" mainTableSchema = "card" mainTableName = "application" >
    <components>
    <component name = "get">
    </component>
    </components>  
    </object>'
SELECT b.value('../../@name', 'NVARCHAR(50)') AS objectName,
       b.value('@name', 'NVARCHAR(50)') AS actionName
FROM @dataModel.nodes('/object/components/component') AS a(b)

Here the output I get is objectName = cardApplication and actionName = get.

I'm new to xquery and wonder how the objectName is returned from this piece of line :

"b.value('../../@name', 'NVARCHAR(50)') AS objectName"

and why not this :

"b.value('../@name', 'NVARCHAR(50)') AS objectName"

I thought since the 'name' field in root element I can use '..@/name', but its give me NULL. someone please explain what does '../../' shorthand used for/ how to use it? any links to understand this?

Answer 1

The .. effectively means go back one level. The alias b is refering to the level /object/components/component. So, if you go back one level (using a single ..) you end up at the node components (specifically /object/components). Thus with b.value('../@name', 'NVARCHAR(50)') you would be trying to return the value of name for the components node; which it doesn't have.

Thus, to get back to the object node, you need to go back 2 levels, which means using ../../.

The idea is identical to if you were in command line. For example C:\Windows\System32> ls ..\.. would list the contents of C:\ in Windows, or larnu@desktop:/var/opt/mssql$ ls ../.. would list the contents of /var in Unix.