For XML Explicit

bmains · March 4th, 2004, 01:04 PM

Hello,

I want to create XML explicitly as follows:

<Documents>
  <Document>
    <Name>
    <Path>
  </Document>
  ...
</Documents>

I am working with the query below, as was given me by an example, which I converted to this new syntax:

select 1 as tag,
       NULL as parent,
       Name as [Document!1!Name!element],
       Path as [Document!1!Path!element]
from Documents
order by Name
for xml explicit

Which creates the XML without the Documents root tag. How do I create that? I don't understand "FOR XML" at all.

Thanks,

Brian

pgtips · March 5th, 2004, 04:50 AM

I assume you're using ADO to get this XML back to your app? If so, you specify the root node as a property of the ADO command object. Here's a snippet of VB code for getting the results of a FOR XML query into a stream object:

Code:

    ' code here to open a connection named "con"

    Set strm = New ADODB.Stream
    strm.Open

    Set cmd = New ADODB.Command
    cmd.ActiveConnection = con
    cmd.CommandText = "stored proc name"
    cmd.CommandType = adCmdStoredProc
    cmd.Properties("Output Stream") = strm ' specify the output stream
    cmd.Properties("XML Root") = "Documents" 'specify the root node here

    ' add any parameters here

    cmd.Execute , Options:=adExecuteStream ' use this option

    MsgBox strm.ReadText ' to access the XML string

If you want to load it into an XML DOM, you may be able to specify a DOM object as the "Output Stream" instead. I've never tried it myself - must give it a go sometime.

hth
Phil

joefawcett · March 5th, 2004, 05:05 AM

Or, to encapsulate it all in a stored procedure, add

Code:

SET NOCOUNT ON
SELECT '<Documents>'

before your existing query and

Code:

SELECT '</Documents>'
SET NOCOUNT OFF

after it.

Joe (MVP - xml)

bmains · March 5th, 2004, 04:40 PM

I'm using VBScript; what's the numerical equivalent to "adExecuteStream"?

Thanks for all of your help,

Brian

joefawcett · March 6th, 2004, 05:31 AM

If you are not including adovbs and are able to use the contstants then you can find out any value if you have Excel, Word or one of the other Office applications with VBA. Open a fresh document, go to VBA editor, normally Alt+F11, go to tools | references and check box next to Microsoft ActiveX Data Objects, version 2.5 or later. Okay box closed and press F2, in the dropdown change to ADODB and search for your term, alternatively open the immediate window and type

Code:

?adExecuteStream

--

Joe

bmains · March 8th, 2004, 09:18 AM