post icon

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();
17 febbraio 2009 @ 12:10

No comments yet.

Leave a comment

Leave a Reply

Are you human? Time limit is exhausted. Please reload CAPTCHA.