Get well-formed XML as SQL statement result
Per ricevere un XML well-formed come risultato di una query SQL è possibile usufruire della clausola SQL FOR XML
Ad esempio, lo statement SQL seguente
USE Northwind
GO
![]()
SELECT [CUSTOMER].CustomerID, [CUSTOMER].ContactName, [ORDER].OrderDate
FROM Customers [CUSTOMER]
INNER JOIN Orders [ORDER] ON [CUSTOMER].CustomerID = [ORDER].CustomerID
FOR XML AUTO
ritorna il seguente XML well formed:
<CUSTOMER CustomerID="VINET" ContactName="Paul Henriot">
<ORDER OrderDate="1996-07-04T00:00:00" />
</CUSTOMER>
<CUSTOMER CustomerID="TOMSP" ContactName="Karin Josephs">
<ORDER OrderDate="1996-07-05T00:00:00" /></CUSTOMER>
<CUSTOMER CustomerID="HANAR" ContactName="Mario Pontes">
<ORDER OrderDate="1996-07-08T00:00:00" />
</CUSTOMER>
Quindi è possibile utilizzare un oggetto di tipo System.Xml.XmlReader per leggere i risultati in XML:
SqlCommand ExecuteXMLCommand = new SqlCommand();
ExecuteXMLCommand.Connection = NorthwindConnection;
ExecuteXMLCommand.CommandType = CommandType.Text;
![]()
//Add the For XML Auto
ExecuteXMLCommand.CommandText = "SELECT CustomerID FROM Customers FOR XML Auto";
ExecuteXMLCommand.Connection.Open();
System.Xml.XmlReader reader = ExecuteXMLCommand.ExecuteXmlReader();
![]()
// .. iterate XMLReader..
![]()
reader.Close();
ExecuteXMLCommand.Connection.Close();
Lascia un commento