Applications and XML - SQL Server 2008

If you use SQLXML or ADO.NET, programming using the XML datatype is simple and does not require much explanation. However, if you use ADO together with SQL Native Client to connect with a SQL Server database, you will want to initialize SQL Native Access Client (SNAC) with the DataTypeCompatibility keyword in your connection string. You should set this string to be equal to 80, which specifies that you want to use features such as multiple active result sets, query notifications, user-defined types, and XML datatype support. If you continue to use Microsoft Data Access Components (MDAC), there are no required changes to use the XML datatype.

Both SNAC and MDAC will return XML as text. You could then load the text into an XML document object to parse the XML. For the richest XML experience, you will want to use .NET with SQLXML. The following code shows how to use ADO with data that uses the XML datatype:

Imports ADODB
Const strDataServer = "localhost"
Const strDatabase = "xmldb"
'Create objects
Dim objConn As New Connection
Dim objRs As Recordset
'Create command text
Dim CommandText As String = "SELECT xmlColWithSchema" & _
" FROM xmltbl2" & _
" WHERE pk = 1"
'Create connection string
Dim ConnectionString As String = "Provider=SQLNCLI" & _
";Data Source=" & strDataServer & _
";Initial Catalog=" & strDatabase &
";Integrated Security=SSPI;" &
"DataTypeCompatibility=80"
'Connect to the data source
objConn.Open(ConnectionString)
'Execute the command
objRs = objConn.Execute(CommandText)
Dim irowcount As Integer = 0
'Go through recordset and display
Do While Not objRs.EOF
irowcount += 1
MessageBox.Show("Row " & irowcount & ":" & vbCrLf & vbCrLf &
objRs(0).Value())
objRs.MoveNext()
Loop
'Clean up our objects
objRs.Close()
objConn.Close()
objRs = Nothing
objConn = Nothing

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

SQL Server 2008 Topics