Introduction:
When designing applications using MS SQL Server, we often encounter the need to send multiple rows of data to be modified (inserted, updated, or deleted) to the database. While we know how to retrieve a set of rows from the database to the application, sending multiple rows to the database has been a challenge until SQL Server 2005. In a previous article, we discussed how to send a delimited string to the database for updating multiple rows. In this article, we will explore how to parse XML and convert it into a table format.
Sending XML data to Stored Procedure:
There are two methods to put data into XML: Attribute Based and Element Based. In Attribute Based XML, we enclose our target data in attributes within specific elements. For example, if we want to send the name and salary of multiple employees to the database, we create an XML document like this:
DECLARE @xml XML
SET @xml = N'
'
To retrieve the data from this XML in SQL Server, we can use XQuery functions. The nodes()
function returns a row set that contains logical copies of the XML data. Using the value()
function, we can retrieve multiple values from the row set. Here is an example:
SELECT T.Item.value('@Name', 'VARCHAR(50)') Name,
T.Item.value('@Salary', 'INT') Salary
FROM @xml.nodes('/ROWS/ROW') AS T(Item)
Parsing Element Based XML:
In Element Based XML, we put our target data under elements. For example, if we want to send the name and salary of multiple employees, we create XML like this:
DECLARE @xml XML
SET @xml = N'
Tom
2100
Nuk
2200
Gilbert
2300
Mat
2600
'
To retrieve the data from this XML, we can use the query()
function along with the value()
function. The query()
function takes an XQuery expression as a parameter and returns the specified XML element. Here is an example:
SELECT T.Item.query('./Name').value('.', 'VARCHAR(50)') Name,
T.Item.query('./Salary').value('.', 'INT') Salary
FROM @xml.nodes('/ROWS/ROW') AS T(Item)
Conclusion:
In this article, we discussed how to work with XML data in SQL Server. We explored two approaches: Attribute Based XML and Element Based XML. Both approaches have their pros and cons, and the choice depends on the specific requirements of your application. XML data provides support for larger data sizes and allows for easier parsing using XQuery functions. These techniques can be particularly useful in scenarios where an application works in a disconnected environment, allowing users to save data locally and then update the database when connected.