SQL 2000 database I can't create root element with for xml auto, root... (1 Viewer)

[MBMSOFT]

SQL 2000 database I can't create root element with for xml auto, root "root" with bcp
the xml file is created but root is not created.. Is it common with SQL 2000 database

This works... but root element is not created

exec master..xp_cmdshell 'bcp "select * from mytable" as ROW for xml auto , elements, queryout "c:\file.xml" -T -C 1251 -w -r -t'

This Does not work at all ...

exec master..xp_cmdshell 'bcp "select * from mytable" as ROW  for xml auto , root " rows" , elements, queryout "c:\file.xml" -T -C 1251 -w -r -t'

I can't find out what is wrong
I got syntax error near root..
Pls any help

 

[Rx_]

<sorry, interrupted and left this un-posted for over a day>
It has been years since I was doing a lot of XML with SQL for automated Laboratory submission.

Read a few articles, this should be the basic format that you seem to have in hand:

bcp "SELECT * FROM pubs..authors FOR XML RAW" queryout c:\myfile.xml -Myserver -Myusername -Mypassword -c -r -t

Use your own query string, output file location, server, user and password. I think it is wise to use the -c, -r and -t as lower case parameters to remove default delimiters from the bcp output (e.g. tab, CR-LF). It should also make the XML better formed. In some cases, badly formed XML allows a human to search and replace if necessary.

It looks like your at the point of needing a root XML element.
it will be necessary to add an opening / closing root tag:

rootopen.txt
<root>

rootclose.txt
</root>

And then do this:

bcp "SELECT * FROM pubs..authors FOR XML RAW" queryout c:\myfile.xml -Myserver -Myusername -Mypassword -c -r -t
copy/b rootopen.txt+myfile.xml+rootclose.txt myfile.xml

The process will add root tags and combine everything into one file.