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'