/*
The following TSQL retrieves attribute values from the XML variable.
Attribute names are specified with "@".
*/
DECLARE @x XML
SET @x = '
Nestle Munch
10
11.25
Britania Good Day
15
12.25
'
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)
/*
OUTPUT:
ItemCode category subcategory
-------------------- -------------------- --------------------
A001 FOOD Candies
A002 FOOD Biscuits
(2 row(s) affected)
*/
/*
The following TSQL retrives values from XML nodes.
Note that, here we dont use the "@" sign to indicate that
we need values of nodes not attributes.
*/
DECLARE @x XML
SET @x = '
Nestle Munch
10
11.25
Britania Good Day
15
12.25
'
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)
/*
OUTPUT:
description qty rate
-------------------- ----------- ----------------------
Nestle Munch 10 11.25
Britania Good Day 15 12.25
(2 row(s) affected)
*/
/*
Well, this query retrieves attribute values as well as values
from nodes. Note that attribute values are specified with an "@"
character.
*/
DECLARE @x XML
SET @x = '
Nestle Munch
10
11.25
Britania Good Day
15
12.25
'
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)
/*
OUTPUT:
ItemCode category subcategory description qty rate
-------------------- -------------------- -------------------- -------------------- ----------- ----------------------
A001 FOOD Candies Nestle Munch 10 11.25
A002 FOOD Biscuits Britania Good Day 15 12.25
(2 row(s) affected)
*/
/*
The following example demonstrates how to extract the value
from a given row. This example extracts a value from the first
row.
The first example selects the value from the first row.
The second example adds an alias to the result column.
The third example assigns the result to a variable.
*/
DECLARE @x XML
SET @x = '
Nestle Munch
10
11.25
Britania Good Day
15
12.25
'
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
/*
OUTPUT:
--------------------
A001
(1 row(s) affected)
Code
--------------------
A001
(1 row(s) affected)
Code
--------------------
A001
(1 row(s) affected)
*/
/*
The following example retrieves the value from the second row.
*/
DECLARE @x XML
SET @x = '
Nestle Munch
10
11.25
Britania Good Day
15
12.25
'
SELECT @x.value('(/orderInfo/item/@code)[2]', 'VARCHAR(20)')
/*
OUTPUT:
--------------------
A002
(1 row(s) affected)
*/
/*
The following example retrieves the value of an
element from the first row.
*/
DECLARE @x XML
SET @x = '
Nestle Munch
10
11.25
Britania Good Day
15
12.25
'
SELECT @x.value('(/orderInfo/item/description)[1]', 'VARCHAR(20)')
/*
OUTPUT:
--------------------
Nestle Munch
(1 row(s) affected)
*/
/*
The following example retrieves the value of an
element from the second row.
*/
DECLARE @x XML
SET @x = '
Nestle Munch
10
11.25
Britania Good Day
15
12.25
'
SELECT @x.value('(/orderInfo/item/description)[2]', 'VARCHAR(20)')
/*
OUTPUT:
--------------------
Britania Good Day
(1 row(s) affected)
*/
/*
Now let us have a look at filtering results. The following
example applies a filter on an attribute value.
*/
DECLARE @x XML
SET @x = '
Nestle Munch
10
11.25
Britania Good Day
15
12.25
'
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'
/*
OUTPUT:
ItemCode subcategory description qty
-------------------- -------------------- -------------------- -----------
A002 Biscuits Britania Good Day 15
(1 row(s) affected)
*/
/*
The following example applies a filter on the value
of an element.
*/
DECLARE @x XML
SET @x = '
Nestle Munch
10
11.25
Britania Good Day
15
12.25
'
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'
/*
OUTPUT:
ItemCode subcategory description qty
-------------------- -------------------- -------------------- -----------
A002 Biscuits Britania Good Day 15
(1 row(s) affected)
*/