-- Declare XML variable
DECLARE @data XML;
-- Element-centered XML
SET @data =
N'<data>
<customer>
<id>1</id>
<name>Allied Industries</name>
</customer>
<customer>
<id>2</id>
<name>Trades International</name>
</customer>
</data>';
-- Using the query() method
SELECT T.customer.query('id').value('.', 'INT') AS customer_id,
T.customer.query('name').value('.', 'VARCHAR(20)') AS customer_name
FROM @data.nodes('data/customer') AS T(customer);
-- Using the value() method
SELECT T.customer.value('(id)[1]', 'INT') AS customer_id,
T.customer.value('(name)[1]', 'VARCHAR(20)') AS customer_name
FROM @data.nodes('data/customer') AS T(customer);
-- Select only customer which id equals 2 using the exist() method
SELECT T.customer.value('(id)[1]', 'INT') AS customer_id,
T.customer.value('(name)[1]', 'VARCHAR(20)') AS customer_name
FROM @data.nodes('data/customer') AS T(customer)
WHERE T.customer.exist('id/text()[. = "2"]') = 1;
-- Attribute-centered XML
SET @data =
N'<data>
<customer id="1" name="Allied Industries"/>
<customer id="2" name="Trades International"/>
</data>';
-- Using the value() method
SELECT T.customer.value('@id', 'INT') AS customer_id,
T.customer.value('@name', 'VARCHAR(20)') AS customer_name
FROM @data.nodes('data/customer') AS T(customer);
-- Results
customer_id customer_name
----------- --------------------
1 Allied Industries
2 Trades International
Labels: t-sql programming, xml