Fetching XML from SQL Server using LINQ to SQL by ThinqLinq

Fetching XML from SQL Server using LINQ to SQL

With SQL Server, you can use the For Xml clause (read more in BOL). The quickest option is to add For XML Auto at the end of a SQL statement. You can do this with dynamic SQL or inside a stored proc. If you use a stored proc, the DBML tool doesn't recognize this as XML (and return it as an XElement as it does for XML Data type columns).

Regardless of whether you are using stored procs or dynamic SQL, the server returns the result as an array of strings broken up into 4000 character chunks. It is your responsibility to piece this back together. You can concatenate the strings and parse the XML, however there is no true root node in this return set, only a series of XML elements.

Since you are not going to be able to rely on the generated method stub for the procedure, you may want to consider using ExecuteQuery directly and handle the string parsing. If you define this in a partial class for your context, it will appear to calling code as if it came directly from the database pre formatted.  For example, here is some code that returns the customers from Northwind as an XElement:

Public Function CustomerAsXml() As XElement
    Dim returnVal = Me.ExecuteQuery(Of String)("Select * from Customers For XML Auto")
    Dim fullString = String.Concat((From x In returnVal Select x).ToArray)
    Dim xml = XElement.Parse("<root>" & fullString & "</root>")
    Return Xml
End Function

You could substitute the name of your stored proc with parameters in the place of this dynamic SQL and it should work equally well.

Dim returnVal = Me.ExecuteQuery(Of String)("CustomersXml", New Object() {})
Posted on - Comment
Categories: LINQ - VB - VB Dev Center - Linq to XML -
comments powered by Disqus