| SQL Server enables you to retrieve the results of any query as XML rather than as a SQL result set. To do this, you use the Microsoft-specific FOR XML clause. You can use a variety of options in the FOR XML clause to customize the XML that SQL Server generates. The first option is FOR XML RAW. When you use raw mode with FOR XML, SQL Server returns one element (always named row) for each row of the result set, with the individual columns represented as attributes. For example, consider this query: SELECT Customers.CustomerID, Customers.CompanyName, Orders.OrderID, Orders.OrderDate FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID WHERE Country = 'Brazil' AND OrderDate BETWEEN '1997-03-15' AND '1997-04-15' FOR XML RAW If you execute this query (for example, using SQL Query Analyzer) in the Northwind sample database, you'll get back these results: <row Customer CompanyName="Ricardo Adocicados" Order OrderDate="1997-03-20T00:00:00"/> <row Customer CompanyName="Queen Cozinha" Order OrderDate="1997-03-26T00:00:00"/> <row Customer CompanyName="Comércio Mineiro" Order OrderDate="1997-04-02T00:00:00"/> <row Customer CompanyName="Tradição Hipermercados" Order OrderDate="1997-04-04T00:00:00"/> The second variant of the FOR XML clause is FOR XML AUTO. When you use auto mode with FOR XML, nested tables in the result set are represented as nested elements in the XML. Columns are still represented as attributes. For example, here's a query that uses FOR XML AUTO: SELECT Customers.CustomerID, Customers.CompanyName, Orders.OrderID, Orders.OrderDate FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID WHERE Country = 'Brazil' AND OrderDate BETWEEN '1997-03-15' AND '1997-04-15' FOR XML AUTO There's a second variant of FOR XML AUTO. You can include the ELEMENTS option to represent columns as elements rather than as attributes. Here's a query that uses this option: SELECT Customers.CustomerID, Customers.CompanyName, Orders.OrderID, Orders.OrderDate FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID WHERE Country = 'Brazil' AND OrderDate BETWEEN '1997-03-15' AND '1997-04-15' FOR XML AUTO, ELEMENTS The final variant of FOR XML is FOR XML EXPLICIT. In explicit mode, you must construct your query so as to create a result set with the first column named Tag and the second column named Parent. These columns create a self-join in the result set that is used to determine the hierarchy of the created XML file. Here's a relatively simple query in explicit mode: SELECT 1 AS Tag, NULL AS Parent, Customers.CustomerID AS [Customer!1!CustomerID], Customers.CompanyName AS [Customer!1!CompanyName], NULL AS [Order!2!OrderID], NULL AS [Order!2!OrderDate] FROM Customers WHERE COUNTRY = 'Brazil' UNION ALL SELECT 2, 1, Customers.CustomerID, Customers.CompanyName, Orders.OrderID, Orders.OrderDate FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID WHERE Country = 'Brazil' AND OrderDate BETWEEN '1997-03-15' AND '1997-04-15' ORDER BY [Customer!1!CustomerID], [Order!2!OrderID] FOR XML EXPLICIT Explicit mode gives you the finest control over the generated XML, but it's also the most complex mode to use in practice. You should stick to raw or auto mode whenever possible. Finally, you can generate schema information as part of a SQL Server query by including the XMLDATA option in the query. You can do this in any of the FOR XML modes. For example, here's a query that you saw earlier in this section, with the XMLDATA option added: SELECT Customers.CustomerID, Customers.CompanyName, Orders.OrderID, Orders.OrderDate FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID WHERE Country = 'Brazil' AND OrderDate BETWEEN '1997-03-15' AND '1997-04-15' FOR XML AUTO, ELEMENTS, XMLDATA Using ExecuteXmlReader() Method ADO.NET provides a means of integrating SQL Server's XML capabilities with the .NET Framework classes. The ExecuteXmlReader() method of the SqlCommand object enables you to retrieve an XmlReader directly from a SQL statement, provided that the SQL statement uses the FOR XML clause as shown in the following code segment: SqlCommand cmd = sqlConnection1.CreateCommand(); // Create a command to retrieve XML cmd.CommandType = CommandType.Text; cmd.CommandText = "SELECT Customers.CustomerID, Customers.CompanyName," + "Orders.OrderID, Orders.OrderDate FROM Customers INNER JOIN Orders " + "ON Customers.CustomerID = Orders.CustomerID WHERE Country = 'Brazil' " + "AND OrderDate BETWEEN '1997-03-15' AND '1997-04-15' " + "FOR XML AUTO, ELEMENTS"; sqlConnection1.Open(); // Read the XML into an XmlReader XmlReader xr = cmd.ExecuteXmlReader(); StringBuilder sbNode = new StringBuilder(); // Dump the contents of the reader while(xr.Read()) { // Process nodes here } // Clean up xr.Close(); sqlConnection1.Close(); Updating SQL Server Data by Using XML You can also update SQL Server data by using special XML messages called DiffGrams. The .NET Framework uses DiffGrams internally as a means of serializing changes in a DataSet object. For example, if you pass the changes in a DataSet object from one tier to another, the .NET Framework uses a DiffGram to send the changes. You can also use DiffGrams yourself to update data in SQL Server. However, before you can do so, you need to install some additional software. This software is the SQLXML Managed Classes, an interface between SQL Server and the .NET Framework, and it can be downloaded from the SQLXML home page: www.msdn.com/sqlxml. After you've installed SQLXML, you can use the SqlXmlCommand object to execute a DiffGram. Take the following steps to learn how to execute a DiffGram: Add a new Windows application project (Example3_5) to the solution. Add a Button control (btnUpdate) to the form. Add a reference to the following SQLXML .NET assembly in your project. (By default, this assembly is installed at C:\Program Files\SQLXML 3.0\bin\Microsoft.Data.SqlXml.dll.) Switch to Code view and add the following using directives: using Microsoft.Data.SqlXml; using System.IO; Double-click the Button control and add the following code to its event handler: private void btnUpdate_Click(object sender, System.EventArgs e) { SqlXmlCommand sxc = new SqlXmlCommand("Provider=SQLOLEDB;" + @"Server=(local)\NetSDK;database=Northwind;" + "Integrated Security=SSPI"); // Set up the DiffGram sxc.CommandType = SqlXmlCommandType.DiffGram; sxc.SchemaPath = @"..\..\diffgram.xsd"; FileStream fs = new FileStream(@"..\..\diffgram.xml", FileMode.Open); sxc.CommandStream = fs; try { // And execute it sxc.ExecuteNonQuery(); MessageBox.Show("Database was updated!"); } catch (SqlXmlException ex) { ex.ErrorStream.Position = 0; string strErr = (new StreamReader(ex.ErrorStream).ReadToEnd()); MessageBox.Show(strErr); } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { fs.Close(); } } Add a new XML file (diffgram.xml) to the project and add the following code: <?xml version="1.0" standalone="yes"?> <diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr= "urn:schemas-microsoft-com:xml-diffgram-v1"> <NewDataSet> <Customers diffgr: msdata:rowOrder="0" diffgr:hasChanges="modified"> <CustomerID>ALFKI</CustomerID> <ContactName>Maria</ContactName> </Customers> </NewDataSet> <diffgr:before> <Customers diffgr: msdata:rowOrder="0"> <CustomerID>ALFKI</CustomerID> <ContactName>Maria Anders</ContactName> </Customers> </diffgr:before> </diffgr:diffgram> Query the Customers table of the Northwind database and make sure that the ContactName for the CustomerID "ALFKI" is set to "Maria Anders". Add a new schema file (diffgram.xsd) to the project and add the following code: <xsd:schema xmlns:xsd= "http://www.w3.org/2001/XMLSchema" xmlns:sql="urn:schemas-microsoft-com:mapping-schema"> <xsd:element name="Customers" sql:relation="Customers" > <xsd:complexType> <xsd:sequence> <xsd:element name="CustomerID" sql:field="CustomerID" type="xsd:string" /> <xsd:element name="ContactName" sql:field="ContactName" type="xsd:string" /> </xsd:sequence> </xsd:complexType> </xsd:element> </xsd:schema> Build and run the project. Click the button to update your SQL Server Northwind database. You can verify that the update worked by running a SELECT query in SQL Query Analyzer.
DiffGrams can insert or delete data as well as modify data. For an insertion, the DiffGram contains the data for the new row and no old data. For a deletion, the DiffGram contains the row to be deleted but no new row. | 
