Overview:Our need is to execute a stored procedure similiar to the following:Select lookupcode, description from statemaster order by description forXML AUTO, ELEMENTSWe want our data tier to pull this information back as an XML result.This poses several questions:1) What is the syntax to get a properly formatted XML string? With the abovesyntax we are finding that the data comes back formatted like so (cut andformatted for readability):<statemaster><lookupcode>AL</lookupcode><description>Alabama</description></statemaster>... cut for length ...<statemaster><lookupcode>WY</lookupcode><description>Wyoming</description></statemaster>What we want to return is something like this:<statelist><statemaster><lookupcode>AL</lookupcode><description>Alabama</description></statemaster>... cut for length ...<statemaster><lookupcode>WY</lookupcode><description>Wyoming</description></statemaster></statelist>The second question is how do you, using Visual Basic 6 and ADO (2.6 - installswith SQL Server 2000 Beta 2) retrieve this XML result? We have found limiteddocumenation on it (in C) but converting to VB does not work (error 3251- Object or provider is not capable of performing requested operation).Below is a snippet of code we converted from a C example (since I am notfluent in C I just very well may have missed something)
im objCmd As ADODB.CommandDim objStream As ADODB.StreamSet objCmd = New ADODB.CommandSet objStream = New ADODB.StreamobjCmd.ActiveConnection = "Provider=SQLOLEDB;Server=SVRTFOUNTAIN\TFOUNTAIN_2000;Database=autoenroll;Trusted_Connectio n=yes;"objStream.OpenobjStream.WriteText "select lookupcode, description from statemasterfor xml auto, elements", adWriteCharobjStream.Position = 0Set objCmd.CommandStream = objStreamobjCmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}"objCmd.Execute , , adExecuteStreamThanks in advance,Tony Fountain, MCPDigital Horizons, Inc.Senior Application Developer
im objCmd As ADODB.CommandDim objStream As ADODB.StreamSet objCmd = New ADODB.CommandSet objStream = New ADODB.StreamobjCmd.ActiveConnection = "Provider=SQLOLEDB;Server=SVRTFOUNTAIN\TFOUNTAIN_2000;Database=autoenroll;Trusted_Connectio n=yes;"objStream.OpenobjStream.WriteText "select lookupcode, description from statemasterfor xml auto, elements", adWriteCharobjStream.Position = 0Set objCmd.CommandStream = objStreamobjCmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}"objCmd.Execute , , adExecuteStreamThanks in advance,Tony Fountain, MCPDigital Horizons, Inc.Senior Application Developer