| description | title | ms.date | dev_langs | ms.assetid | ||
|---|---|---|---|---|---|---|
Learn more about: SQL XML Column Values |
SQL XML Column Values |
03/30/2017 |
|
d97ce4da-f09c-4d1e-85b7-a0ccedd7246a |
SQL Server supports the xml data type, and developers can retrieve result sets including this type using standard behavior of the xref:System.Data.SqlClient.SqlCommand class. An xml column can be retrieved just as any column is retrieved (into a xref:System.Data.SqlClient.SqlDataReader, for example) but if you want to work with the content of the column as XML, you must use an xref:System.Xml.XmlReader.
The following console application selects two rows, each containing an xml column, from the Sales.Store table in the AdventureWorks database to a xref:System.Data.SqlClient.SqlDataReader instance. For each row, the value of the xml column is read using the xref:System.Data.SqlClient.SqlDataReader.GetSqlXml%2A method of xref:System.Data.SqlClient.SqlDataReader. The value is stored in an xref:System.Xml.XmlReader. Note that you must use xref:System.Data.SqlClient.SqlDataReader.GetSqlXml%2A rather than the xref:System.Data.IDataRecord.GetValue%2A method if you want to set the contents to a xref:System.Data.SqlTypes.SqlXml variable; xref:System.Data.IDataRecord.GetValue%2A returns the value of the xml column as a string.
Note
The AdventureWorks sample database is not installed by default when you install SQL Server. You can install it by running SQL Server Setup.
[!code-csharp[DataWorks SqlClient.GetXmlDataReader#1](../../../../../samples/snippets/csharp/VS_Snippets_ADO.NET/DataWorks SqlClient.GetXmlDataReader/CS/source.cs#1)] [!code-vb[DataWorks SqlClient.GetXmlDataReader#1](../../../../../samples/snippets/visualbasic/VS_Snippets_ADO.NET/DataWorks SqlClient.GetXmlDataReader/VB/source.vb#1)]
- xref:System.Data.SqlTypes.SqlXml
- XML Data in SQL Server
- ADO.NET Overview