SQL XML .NET Data Provider

SQL XML .NET Data Provider

SqlXmlCommand belongs to Microsoft.Data.SqlXml namespace, which is not part of .NET framework. The extra functionality SqlXmlCommand provides are: you can send a XPATH query instead of a SELECT SQL query to database to retrieve data, and submit changes to database using a diffgram XML document.

When you provide a XPATH to the command, it converts it to a “FOR XML” SQL query. When you submit a diffgram, the command generates a batch of SQL queries wrapped in a transaction. In both cases the command needs to know the schema of the table to be able to generate those SQL queries, so you need to provide the command with a XSD file:

' Retrieve data using XPATH

Dim cmd As New SqlXmlCommand(“Orders[CustomerID=’GROSR’]”, strConn)

cmd.SchemaPath = “C:\MySchema.xsd”

cmd.CommandType = SqlXmlCommandType.XPath

Dim rdr As XmlReader = cmd.ExecuteXmlReader()

mds.ReadXml(rdr)

' Do some changes

…

' Write the changed dataset into a DiffGram

mds.WriteXml(“C:\MyDiffGram.xml”, XmlWriteMode.DiffGram)

' Submit the DiffGram

cmd = New SqlXmlCommand(strConn)

cmd.SchemaPath = “C:\MySchema.xsd”

cmd.CommandType = SqlXmlCommandType.DiffGram

cmd.CommandStream = New FileStream(“C:\MyDiffGram.xml”, FileMode.Open, FileAccess.Read)

cmd.ExecuteNonQuery