XML, ADO, and VB6 3

[ousoonerjoe]

I have been looking around at how to use ADO in VB6 to read/write XML. The source file is XML, the destination is SQL 2000, and will need to output a recordset to XML. I need to do some scrubbing of the data before importing otherwise i would just make DTS do all the work. I vaguely remember ADO 2.5 and better, being available to use for XML. I found a site that got into how to do the read aspect. It looks like it would work, but the "Source XML file is incomplete or invalid" error appears. I am very unfamiliar with XML, but am eager to do this project and learn more about it.

I have sifted through many of the other topics on this board, but each seem to lead me into the same type of error.

Here is a snip it of the XML file. (Other fields omitted from sample for ease of reference.):

<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
	<w5file PostTransferClaimID="12821" Claimid="31625" PolicyID="629" />
	<w5file PostTransferClaimID="13190" Claimid="29834" PolicyID="49162"/>
	<w5file Claimid="32291" PolicyID="22766" />
	<w5file PostTransferClaimID="11461" Claimid="30828" PolicyID="29376"/>
</ROOT>

Function used to access XML with ADO:

Public Function LoadRsFromXML(FullPath As String) As ADODB.Recordset
    Dim Rst As ADODB.Recordset
    Set Rst = New ADODB.Recordset
    Rst.Open FullPath, "Provider=MSPersist;", adOpenForwardOnly, adLockReadOnly, adCmdFile
End Function

ADO and MSXML both seem to be "puking" on this. Any tips or ideas are welcome and appreciated.

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.

 

[gmmastros]

>> I need to do some scrubbing of the data before importing

Can you do that scrubbing from within the database?

I would suggest that you import the XML right in to SQL Server (using a stored procedure). Then, do your scrubbing/inserting/whatever else. You can even make the stored procedure return XML.

If you'd like to explore doing it this way... I'm willing to help. Just let me know.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[JoeAtWork]

The X in XML stands for eXtensible. That refers to the fact that you can define the XML any way you like. There's no one XML format, there's thousands (if not millions) existing, and new ones built each day.

As far as I know, the only XML that ADO can read is the kind that it saves when it persists a recordset. You can't just take any XML file and expect to be able to load it into a recordset.

You could read the XML file in with standard file handling functions.
However, it will almost certainly be more productive to use one of the existing XML parsers. Here's one for VB:

http://msdn2.microsoft.com/en-us/library/aa468547.aspx

I think you will need to get at least a beginners knowledge of XML to understand how to use the parser. It's not too difficulat once you get the basic concepts.

 

[ousoonerjoe]

I need to be able to export in the same format as the sample i gave above. If that is possible, I'm all for doing it in DTS and letting the database do all the work.

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.

 

[gmmastros]

SQL2000 has an XML parser built in.

You may need to install XML 2 separately in order to use it.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

First... Open Query Analyzer.
Open a New Window
Copy/Paste this code....

[COLOR=blue]Create[/color] [COLOR=blue]Procedure[/color] TestXMLInput
    @XML [COLOR=blue]Text[/color]
[COLOR=blue]As[/color]
[COLOR=blue]SET[/color] [COLOR=#FF00FF]NOCOUNT[/color] [COLOR=blue]ON[/color]

[COLOR=blue]Declare[/color] @iDoc [COLOR=blue]Integer[/color]
[COLOR=blue]Exec[/color] sp_xml_preparedocument @iDoc [COLOR=blue]OUTPUT[/color], @XML

[COLOR=blue]Select[/color]  PostTransferClaimID,
        Claimid,
        PolicyID
[COLOR=blue]From[/color]    OpenXML(@iDoc, [COLOR=red]'//ROOT/w5file'[/color], 1)
[COLOR=blue]With[/color]    (
        PostTransferClaimID [COLOR=blue]Int[/color],
        Claimid [COLOR=blue]Int[/color],
        PolicyID [COLOR=blue]int[/color]
        )


[COLOR=blue]Exec[/color] sp_xml_removedocument @iDoc

You can then test it like this...

[COLOR=blue]exec[/color] TestXMLInput[COLOR=red]'
[/color]<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
    <w5file PostTransferClaimID="12821" Claimid="31625" PolicyID="629" />
    <w5file PostTransferClaimID="13190" Claimid="29834" PolicyID="49162"/>
    <w5file Claimid="32291" PolicyID="22766" />
    <w5file PostTransferClaimID="11461" Claimid="30828" PolicyID="29376"/>
</ROOT>[COLOR=red]'[/color]

You can easily return XML from SQL 2000. Just put For XML AUTO at the end of your query.

Ex:

Select Columns....
From   Table
FOR XML AUTO

Hope this helps.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ousoonerjoe]

Update: Haven't disappeared, just building the stored procedures for a moving target. Love it when the client changes requirements in mid-stream.

Is there a statement that needs to be added to force the output to a file with a specified path and name?

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.

 

[gmmastros]

Well... there is gets a little weird.

If this is a 'once and done' thing, you could use query analyzer to save the data to a text file. This is likely to be your easiest option. Click Query -> Results to File.

There is a system stored procedure call sp_makewebtask. You can use this to export your XML to a file. But, it's difficult to get the permissions right, and Microsoft recommends that you NOT use this command because they will be removing it from future versions on SQL Server.

You could write a SQL Server User Defined Function that can save data to a file. This can be problematic because you still have permission issues to deal with and SQL Server 2005 disables sp_oa functions, so you may have a difficult time getting them enabled (especially if you don't have control of the server). To see how this might be done...

http://www.sqlservercentral.com/art...ileusingthesp_oacreatestoredprocedurean/1694/

You could use osql (or sqlcmd for sql server 2005) in command line to export data to a file. Again, you might run in to security problems, but this method is probably easier than any other mentioned.

Lastly, you could return the XML data to VB through an ADO recordset object. Then, use any old method for saving the data to a file. This is probably your best and simplest approach and is the one I recommend you use.

If you pick one of these approaches and have a difficult time implementing it, let me know and I will attempt to help you more.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[BobRodes]

<Then, use any old method for saving the data to a file.

Of the many methods available, I would recommend the Save method of the ADO Recordset object as the most straightforward. For more information on the Save method, check

http://msdn2.microsoft.com/en-us/library/ms681501(VS.85).aspx.

 

[ousoonerjoe]

WOW... i was really hoping to make the export completely DTS, but looks like the best option will be to make the stored proc kick out an XML recordset and then pass that to the DOM parser and save it in VB6. On a good note, that should be pretty quick and simple to do.

SQL 2005 is a lot more readily available for this kind of task isn't it?

Thank you Bob, gmmastros, JoeAtWork for all your assistance. I'll post a code snip-it when i complete for any future inquires that hit the same wall i did.

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.

 

[BobRodes]

Don't assume that the xml structure coming out of the stored proc is compatible with the input structure for an ADO recordset. The structure for ADO has to be exact. If you run into problems, I would suggest that you create the same recordset in the SP and in the RS, and compare the resulting xml files. You may need to do some work.

 

[ousoonerjoe]

Good call, Bob. Could i then just take a "normal" recordset, pass it to the DOM object and generate the export from there? Then i would be able to have a little more control over the format rather than just letting MS and ADO give me what ever they come up with.

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.

 

[gmmastros]

I think you're missing the point. When you use the FOR XML clause in a stored procedure, you will get a recordset with 1 row and 1 column. It will contain your XML data. Just save that to a file and you're done.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ousoonerjoe]

Does it omit fields (parameters) if they have a NULL value? That was something i noticed in my tests.

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.

 

[gmmastros]

Yes. It will omit the attributes if the value is null.

Ex:

Declare @Temp Table (Id int, data varchar(20))

Insert Into @Temp Values(1, 'red')
Insert Into @Temp Values(2, NULL)
Insert Into @Temp Values(3, 'Blue')

Select * From @Temp As MySuperDooperTable For XML Auto

The XML it generates is...

[tt][blue]
<MySuperDooperTable Id="1" data="red" />
<MySuperDooperTable Id="2" />
<MySuperDooperTable Id="3" data="Blue" />
[/blue][/tt]

You can, of course, cause it to output empty elements by making use of the Coalesce function, like this...

[COLOR=blue]Declare[/color] @Temp [COLOR=blue]Table[/color] (Id [COLOR=blue]int[/color], data [COLOR=blue]varchar[/color](20))

[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](1, [COLOR=red]'red'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](2, NULL)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](3, [COLOR=red]'Blue'[/color])

[COLOR=blue]Select[/color] [COLOR=#FF00FF]Coalesce[/color](Id, 0) [COLOR=blue]As[/color] Id,
       [COLOR=#FF00FF]Coalesce[/color](Data, [COLOR=red]''[/color]) [COLOR=blue]As[/color] Data
[COLOR=blue]From[/color]   @Temp [COLOR=blue]As[/color] MySuperDooperTable 
[COLOR=blue]For[/color] XML Auto

Which outputs this...

[tt][blue]
<MySuperDooperTable Id="1" Data="red" />
<MySuperDooperTable Id="2" [!]Data="" [/!]/>
<MySuperDooperTable Id="3" Data="Blue" />
[/blue][/tt]

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom