Sending multiple values in XML to database from application and parse using XQuery in SQL Server 2005

SQL Server 2005 has XML support, it means we can process XML on database side. There are some scenarios that we want to insert, update or delete multiple rows or send some different values to the database, one way is using delimited values and then on database side, we have to parse that string to retrieve the values, another way is using XML to send data to database, then in the stored procedure we can use XQuery and easily parse that XML.(the names are all case sensitive)


let's have an example.

Declare @myXML XML

SET @myXML = N'<rows>
<row Model="toyota" Year="2005" />
<row Model="mazda" Year="2008" />
<row Model="ford" Year="2000" />
</rows>'

SELECT T.Item.value('@Model','varchar(20)') AS Model,
T.Item.value('@Year','varchar(20)') AS Year
FROM @myXML.nodes('/rows[1]/row') AS T(Item)

OR

Declare @myXML XML

SET @myXML = N'<rows>
<row>
<Model>toyota</Model>
<Year>2007</Year>
</row>
<row>
<Model>ford</Model>
<Year>2005</Year>
</row>
</rows>'


SELECT T.Item.query('./Model').value('.', 'VARCHAR(20)') AS Model,
     T.Item.query('./Year').value('.', 'VARCHAR(20)') AS Year
FROM @myXML.nodes('/rows[1]/row') AS T(Item)