In the following microsoft documentation: -
https://docs.microsoft.com/en-us/sql/ado/guide/appendixes/microsoft-data-shaping-service-for-ole-db-ado-service-provider?view=sql-server-2017
this feature is being removed and the suggestion is to use XML. Has anyone done this? I'm wondering what they mean, in terms of loading the structure of what MSDataShape is by using XML, or just to use XML objects?
TIA
I believe this is referring to the FOR XML clause of T-SQL, which performs much the same job as MSDataShape in that it returns hierarchically nested data.
Port your MSDataShape queries to FOR XML queries and change the client to parse the results instead of using the MSDataShape OLEDB provider.
At the client side, SAX or pull parsing would be the best fit to port code that previously used MSDataShape (which also had a move-through-the-records cursor based model).
This is my bit of code that is helpful. My MSDataShape code still works, therefore I propose using that to generate your XML as a template, then use that going forward to load them: -
Dim objShapeMaker As clsShapeMaker
Dim rsoTemp As ADODB.Recordset
Dim strXMLTemplate As String
' Template file
strXMLTemplate = "C:\Temp\Template_GI.xml"
' Create the MSDataShape and save it to XML
Set rsoTemp = objShapeMaker.CreateGI()
rsoTemp.Save strXMLTemplate, adPersistXML
' Now we have the XML in a file going forward, load it in my recordset
Set rsoTemp = New ADODB.Recordset
rsoTemp.Open strXMLTemplate, , , , adCmdFile
' Cleanup
Set rsoTemp = Nothing
Set objShapeMaker = Nothing
If you don't like the idea of generating XML template files to maintain, you could do this via .NET and expose it to COM to use in your VB6/VBA application as mentioned here.
I have made a .NET application that can generate these XML files from simple code lines should anyone want going forward that is similar to the blog listed, however it handles child recordsets with relationships.
EDIT 1: This works great if you have schema set ups without returning data. As far as I can tell, to populate these effectively, it's better to write code to load the structure first, and populate it after from seperate recordsets (which is slower!)
EDIT 2: This is the approach we are taking with a replacement in a .NET Interop. Initially looking at bringing XML from SQL and parsing that back as required. This could be bought back into a DataSet and that's parsed into the target recordset as well, but then the relationship between the tables in the result dataset needs to be set in code rather than the one place in T-SQL with XML output.
