Things are getting better and better. And with the comments on the discussion even better. Last comments and questions from bdba where helpfull too as my final goal is to accomplish something similar to what he was trying to do.

Just in case, here is again the code with comments in spanish. The link to the code on each example works fine 😉

DECLARE @x XML

SET @x = '<orderInfo>

<item code="A001" category="FOOD" subcategory="Candies">

<description>Nestle Munch</description>

<qty>10</qty>

<rate>11.25</rate>

</item>

<item code="A002" category="FOOD" subcategory="Biscuits">

<description>Britania Good Day</description>

<qty>15</qty>

<rate>12.25</rate>

</item>

</orderInfo>'

--la query a continuación lista los valores de los atributos de la variable XML declarada

--los nombres de los atributos los referenciamos poniendo @ delante del mismo

SELECT

x.item.value('@code[1]', 'VARCHAR(20)') AS ItemCode,

x.item.value('@category[1]', 'VARCHAR(20)') AS category,

x.item.value('@subcategory[1]', 'VARCHAR(20)') AS subcategory

FROM @x.nodes('//orderInfo/item') AS x(item)

--ahora queremos los valores de los nodos, para esto no necesitamos @ como en el caso anterior

SELECT

x.item.value('description[1]', 'VARCHAR(20)') AS description,

x.item.value('qty[1]', 'INT') AS qty,

x.item.value('rate[1]', 'FLOAT') AS rate

FROM @x.nodes('//orderInfo/item') as x(item)

--veamos como obtener todos los valores, tanto de los atributos como los nodos y listarlos en una tabla

--notar que usamos @ para referenciar los valores de los atributos como en el primer ejemplo

SELECT

x.item.value('@code[1]', 'VARCHAR(20)') AS ItemCode,

x.item.value('@category[1]', 'VARCHAR(20)') AS category,

x.item.value('@subcategory[1]', 'VARCHAR(20)') AS subcategory,

x.item.value('description[1]', 'VARCHAR(20)') AS description,

x.item.value('qty[1]', 'INT') AS qty,

x.item.value('rate[1]', 'FLOAT') AS rate

FROM @x.nodes('//orderInfo/item') AS x(item)

--pero quizás nos interesa solo una fila, no todas

--veamos diferentes formas de como hacerlo, en este caso solo tomamos la 1ra fila, el valor del campo 'code'

SELECT @x.value('(/orderInfo/item/@code)[1]', 'VARCHAR(20)')

SELECT @x.value('(/orderInfo/item/@code)[1]', 'VARCHAR(20)') AS Code

DECLARE @code VARCHAR(20)

SELECT @code = @x.value('(/orderInfo/item/@code)[1]', 'VARCHAR(20)')

SELECT @code as Code

--si queremos los valores del campo 'code' de la 2da fila

SELECT @x.value('(/orderInfo/item/@code)[2]', 'VARCHAR(20)')

--busquemos ahora el valor de un nodo, no de un atributo

--en este caso 'description' en la 1ra fila, notese que no usamos @ para referenciar el campo

SELECT @x.value('(/orderInfo/item/description)[1]', 'VARCHAR(20)')

--igual pero en la 2da fila

SELECT @x.value('(/orderInfo/item/description)[2]', 'VARCHAR(20)')

--ahora apliquemos un filtro, busquemos los valores solo del item 'A002'

SELECT x.item.value('@code[1]', 'VARCHAR(20)') AS ItemCode,

x.item.value('@subcategory[1]', 'VARCHAR(20)') AS subcategory,

x.item.value('description[1]', 'VARCHAR(20)') AS description,

x.item.value('qty[1]', 'INT') AS qty

FROM @x.nodes('//orderInfo/item') AS x(item)

WHERE x.item.value('@code[1]', 'VARCHAR(20)') = 'A002'

-- y ahora filtremos por el valor de un elemento en los nodos

SELECT x.item.value('@code[1]', 'VARCHAR(20)') AS ItemCode,

x.item.value('@subcategory[1]', 'VARCHAR(20)') AS subcategory,

x.item.value('description[1]', 'VARCHAR(20)') AS description,

x.item.value('qty[1]', 'INT') AS qty

FROM @x.nodes('//orderInfo/item') AS x(item)

WHERE x.item.value('description[1]', 'VARCHAR(20)') = 'Britania Good Day'