SQL Server는 xml 데이터 형식을 지원합니다. 또한 개발자는 SqlCommand 클래스의 표준 동작을 사용하여 이 형식을 포함한 결과 집합을 검색할 수 있습니다. 열을 xml로 검색하는 것처럼, 예를 들어 SqlDataReader로 검색할 수 있습니다. 하지만 열의 내용을 XML로 사용하려면 XmlReader를 사용해야 합니다.
예시
다음 콘솔 애플리케이션은 xml 데이터베이스의 Sales.Store 테이블에서 열을 포함하는 두 개의 행을 선택하여 SqlDataReader 인스턴스로 전송합니다. 각 행에 대해 xml 열의 값은 GetSqlXml의 SqlDataReader 메서드를 사용하여 읽습니다. 값은 XmlReader에 저장됩니다.
GetSqlXml 메서드를 사용하는 대신, 내용을 GetValue 변수로 설정하려면 SqlXml 메서드를 사용해야 합니다. GetValue는 xml 열의 값을 문자열로 반환합니다.
비고
SQL Server를 설치할 때 AdventureWorks 샘플 데이터베이스는 기본적으로 설치되지 않습니다. SQL Server 설치 프로그램을 실행하여 설치할 수 있습니다.
// Example assumes the following directives:
// using System.Data.SqlClient;
// using System.Xml;
// using System.Data.SqlTypes;
static void GetXmlData(string connectionString)
{
using (SqlConnection connection = new(connectionString))
{
connection.Open();
// The query includes two specific customers for simplicity's
// sake. A more realistic approach would use a parameter
// for the CustomerID criteria. The example selects two rows
// in order to demonstrate reading first from one row to
// another, then from one node to another within the xml column.
const string commandText =
"SELECT Demographics from Sales.Store WHERE " +
"CustomerID = 3 OR CustomerID = 4";
SqlCommand commandSales = new(commandText, connection);
SqlDataReader salesReaderData = commandSales.ExecuteReader();
// Multiple rows are returned by the SELECT, so each row
// is read and an XmlReader (an xml data type) is set to the
// value of its first (and only) column.
var countRow = 1;
while (salesReaderData.Read())
// Must use GetSqlXml here to get a SqlXml type.
// GetValue returns a string instead of SqlXml.
{
SqlXml salesXML =
salesReaderData.GetSqlXml(0);
XmlReader salesReaderXml = salesXML.CreateReader();
Console.WriteLine("-----Row " + countRow + "-----");
// Move to the root.
salesReaderXml.MoveToContent();
// We know each node type is either Element or Text.
// All elements within the root are string values.
// For this simple example, no elements are empty.
while (salesReaderXml.Read())
{
if (salesReaderXml.NodeType == XmlNodeType.Element)
{
var elementLocalName =
salesReaderXml.LocalName;
salesReaderXml.Read();
Console.WriteLine(elementLocalName + ": " +
salesReaderXml.Value);
}
}
countRow++;
}
}
}
' Example assumes the following directives:
' Imports System.Data.SqlClient
' Imports System.Xml
' Imports System.Data.SqlTypes
Private Sub GetXmlData(ByVal connectionString As String)
Using connection As SqlConnection = New SqlConnection(connectionString)
connection.Open()
'The query includes two specific customers for simplicity's
'sake. A more realistic approach would use a parameter
'for the CustomerID criteria. The example selects two rows
'in order to demonstrate reading first from one row to
'another, then from one node to another within the xml
'column.
Dim commandText As String = _
"SELECT Demographics from Sales.Store WHERE " & _
"CustomerID = 3 OR CustomerID = 4"
Dim commandSales As New SqlCommand(commandText, connection)
Dim salesReaderData As SqlDataReader = commandSales.ExecuteReader()
' Multiple rows are returned by the SELECT, so each row
' is read and an XmlReader (an xml data type) is set to the
' value of its first (and only) column.
Dim countRow As Integer = 1
While salesReaderData.Read()
' Must use GetSqlXml here to get a SqlXml type.
' GetValue returns a string instead of SqlXml.
Dim salesXML As SqlXml = _
salesReaderData.GetSqlXml(0)
Dim salesReaderXml As XmlReader = salesXML.CreateReader()
Console.WriteLine("-----Row " & countRow & "-----")
' Move to the root.
salesReaderXml.MoveToContent()
' We know each node type is either Element or Text.
' All elements within the root are string values.
' For this simple example, no elements
' are empty.
While salesReaderXml.Read()
If salesReaderXml.NodeType = XmlNodeType.Element Then
Dim elementLocalName As String = _
salesReaderXml.LocalName
salesReaderXml.Read()
Console.WriteLine(elementLocalName & ": " & _
salesReaderXml.Value)
End If
End While
countRow = countRow + 1
End While
End Using
End Sub