Anyone know if it is possible to save an xml file from a stored procedure that uses the FOR XML RAW clause in the same way as objRS.Save with a disconnected recordset?

AvdAStarting Member

5 Posts

Posted - 2001-12-18 : 03:02:43

Thanks Rob Roy ;-) This bcp of XML output has got to be one of the most useful things anyone has figured out. It is simple and brilliant. Now all that remains is for me to concoct a FOR XML EXPLICIT procedure which produces the guts of it and we're away. I can even specify the DTD in my ROOTOPEN text!

rksahuStarting Member

2 Posts

Posted - 2002-04-09 : 15:17:38

quote:I was playing around with this recently, and the method I'm working on uses bcp to export the results into a text file. Something like:bcp "SELECT * FROM pubs..authors FOR XML RAW" queryout c:\myfile.xml -Sserver -Uusername -Ppassword -c -r -tJust replace the query string, output file, server, user and password with your settings. Make sure you use the -c, -r and -t (make sure they are lower case) parameters. This will remove the default delimiters from bcp's output (tab and CR-LF), which was causing my XML to be badly formed.The only problem I found was that it doesn't create a root XML element, so you might have to add an opening and closing root tag to your file. Make up two text files like this:rootopen.txt<root>rootcls.txt</root>And then do this:bcp "SELECT * FROM pubs..authors FOR XML RAW" queryout c:\myfile.xml -Sserver -Uusername -Ppassword -c -r -tcopy rootopen.txt+myfile.xml+rootcls.txt myfile.xmlThat will add the root tags and combine everything into one file.

rksahuStarting Member

2 Posts

Posted - 2002-04-09 : 15:19:30

Hi,When I am trying to run this in query analyzer it is giving Msg 170, is say some problem near queryout. Please let me know how to overcome to this problem

robvolkMost Valuable Yak

15732 Posts

Posted - 2002-04-09 : 15:39:04

What version of SQL Server/bcp are you using? Version 6.5 and earlier of bcp does not support the "queryout" parameter.

senthil_mcStarting Member

1 Post

Posted - 2002-04-26 : 12:19:04

Hi, I am finding the bcp has limitation of 128 characters for the query. This is not enough for specifying a complex Explicit query. Is there any way out? Can we send a qury file instead like osql.Thankssenthil

robvolkMost Valuable Yak

15732 Posts

Posted - 2002-04-26 : 12:25:56

Why not write the query as a stored procedure and use queryout "EXECUTE sp_name" in your bcp command?

mfemenelProfessor Frink

1421 Posts

Posted - 2002-08-13 : 13:45:56

Ok, I'm a little stuck here. I was trying this:bcp "SELECT * FROM pubs..authors FOR XML RAW" queryout c:\myfile.xml -Sserver -Uusername -Ppassword -c -r -t copy rootopen.txt+myfile.xml+rootcls.txt myfile.xml and i can get the file created, but the copy piece isn't working for me. I'm 100% sure i'm doing something stupid I just don't see it. Suggestions?Mike"oh, that monkey is going to pay"

quote:Ok, I'm a little stuck here. I was trying this:bcp "SELECT * FROM pubs..authors FOR XML RAW" queryout c:\myfile.xml -Sserver -Uusername -Ppassword -c -r -t copy rootopen.txt+myfile.xml+rootcls.txt myfile.xml and i can get the file created, but the copy piece isn't working for me. I'm 100% sure i'm doing something stupid I just don't see it. Suggestions?Mike"oh, that monkey is going to pay"

[url][/url][url][/url][url][/url][url][/url]

tadStarting Member

31 Posts

Posted - 2003-03-04 : 17:16:31

How do I had handle odd characters that are in the Northwind database. The problem can be quickly seen in the Products.ProductName column. If you comment out that column and run it the process below creates XML that can be viewed in IE. Add that column back and errors occur that I don't know how to handle. Make sure you create rootopn.txt and rootcls.txt described below.Thanksdrop procedure spXMLTestgo create procedure spXMLTestas/*Syntax: (From cmd prompt)bcp "Exec Northwind.dbo.spXMLTest" queryout c:\test.xml -Szchq_mssql\prd1 -T -c -r -tcopy /b rootopn.txt + test.xml + rootcls.txt test.xml C:\>type rootopn.txt<root>C:\>type rootcls.txt</root>*/select Customers.ContactName,Customers.ContactTitle,Customers.CompanyName,Customers.CustomerID,Orders.OrderID, Orders.OrderDate, Orders.ShippedDate,[Order Details].UnitPrice, [Order Details].Quantity,Products.ProductName, Products.UnitsInStock From Customers, Orders, [Order Details], ProductsWhere Customers.CustomerID = Orders.CustomerIDand Orders.OrderID = [Order Details].OrderID and [Order Details].ProductID = Products.ProductIDand Customers.Country='Uk' for XML AUTO

robvolkMost Valuable Yak

15732 Posts

Posted - 2003-03-04 : 17:35:12

Try using the -w option instead of -c, in all likelihood the odd characters are Unicode that can't be translated. This will affect the format of your XML files though, they may not open properly in IE. If that fails, look at the -C parameter to specify a code page to translate the data. Books Online has more details on the bcp parameters.

tadStarting Member

31 Posts

Posted - 2003-03-05 : 09:59:21

I don't believe creating this XML can be accomplished with the BCP utility. The characters that cause the problem, can be removed by using the -C850, but an unprintable character is added at each replacement point.

robvolkMost Valuable Yak

15732 Posts

Posted - 2003-03-05 : 10:18:51

Where are you getting errors? From bcp? Or from IE when it tries to open the XML file? What is the exact error message you're getting?If it's IE, it may be because it's using a character set that doesn't translate the data correctly, and you may be able to include a setting in the XML header to fix that. I'm not an XML wiz but I'm pretty sure it can do something like that.BTW, I know you're using an example here, but are you actually encountering this problem live, or expect to? If you're not using nchar/nvarchar data types, or you're absolutely certain they won't contain odd characters, then I don't think you need to worry. And this technique was never meant to be a be-all end-all way to get SQL Server data into an XML file, it's just a simple, fast method. The normal way is to use ADO Stream objects from an application layer, read the XML, and then do whatever you need to with the Stream (including writing it to disk)

Arnold FribbleYak-finder General

1961 Posts

Posted - 2003-03-05 : 12:14:48

Unless you say otherwise, your XML file either has to consist of UTF-8 encoded or UTF-16 encoded ISO 10646 characters. Since (AFAIK) BCP can't be persuaded to output UTF-8, that means that you either have to output everything as wide characters with -w or output them as some other 8-bit encoding like -C ACP -c and say so at the top of the XML file.UTF-16:Use -w, make sure rootopen.txt and rootcls.txt are UTF-16 (Notepad on Windows 2000 and XP will read and write in UTF-16 but I don't think they can be persuaded to let you choose, which makes this a bit tricky).ISO Latin-1 -- will lose / downconvert characters outside that repertoire:use -C ACP -c in the BCP, use the original 8-bit-character rootopen.txt and rootcls.txt, but add an extra line at the start of rootopen.txt so it reads:

<?xml version='1.0' encoding='ISO-8859-1'?><root>

however, I think character in the CP-1252 repertoire that aren't in ISO Latin-1 may cause problems.Edited by - Arnold Fribble on 03/05/2003 12:16:35

Hi All,I am trying to use the bcp method to create an XML file from the data in SQL Server 2K. But there is a wierd problem with it. Any idea is welcomed..ThanksBahtiyar KARANLIKbcp statement:EXEC master..xp_cmdshell 'bcp "SELECT CustID,CustName,CustSurname,CustEmail FROM aDB..T_Customers FOR XML RAW" queryout "c:\customers.xml" -fc:\bcp.fmt -SserverName -Usa -Ppwd -C RAW -r -t'Format File:8.041 SQLCHAR 0 9 "\t" 1 CustID Turkish_CI_AS2 SQLCHAR 0 100 "\t" 2 CustName Turkish_CI_AS3 SQLCHAR 0 100 "\t" 3 CustSurname Turkish_CI_AS4 SQLCHAR 0 100 "\t" 4 CustEmail Turkish_CI_ASError String:Error = [Microsoft][ODBC SQL Server Driver]Host-file columns may be skipped only when copying into the ServerAny other way of retrieving data from SQL server is also welcomed. But i will be really glad if the method uses a stored procedure to do that:)

robvolkMost Valuable Yak

15732 Posts

Posted - 2003-10-09 : 13:03:06

You cannot use a format file when outputting XML this way. XML is a serialized string and does not have columns.

jawg62Starting Member

1 Post

Posted - 2003-11-20 : 06:59:57

Hi,I was able to add a root element to my xml without resorting to the "copy rootopen.txt+myfile.xml+rootcls.txt myfile.xml" method suggested by robvolk.To add the root element, I had to modify the query I was using.The original query that produced output without a root element looked something like this:SELECT 1 AS Tag, null AS parent, dateEnd AS [import!1!dateEnd], duration AS [import!1!duration], dateLabel AS [import!1!date], diagFile AS [import!1!diagFile!element], claimFile AS [import!1!claimFile!element],FROM ##filesORDER BY numFOR XML EXPLICITThe modified query that produced output with a root element:SELECT 1 AS Tag, null AS parent, null AS [import!2!dateEnd], null AS [import!2!duration], null AS [import!2!date], null AS [import!2!diagFile!element], null AS [import!2!claimFile!element], 0 AS [root!1!order!hide]UNION ALL SELECT 2 AS Tag, 1 AS parent, dateEnd, duration, dateLabel, diagFile, claimFile, numFROM ##filesORDER BY [root!1!order!hide]FOR XML EXPLICITIt's a little hacky, but it writes a well formed xml document in one step.Jake