| Author |
 Topic  |
|
jackstow
Posting Yak Master
United Kingdom
160 Posts |
 Posted - 09/25/2001 : 10:49:36
|
| 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? |
|
|
robvolk
Most Valuable Yak
USA
15559 Posts |
Posted - 09/25/2001 : 12:07:05
|
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 -t
Just 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 -t
copy/b rootopen.txt+myfile.xml+rootcls.txt myfile.xml
That will add the root tags and combine everything into one file.
<edit> to fix copy command flag /b </edit>
Edited by - robvolk on 10/23/2002 08:22:59 |
 |
|
|
jackstow
Posting Yak Master
United Kingdom
160 Posts |
Posted - 09/27/2001 : 09:21:51
|
Thank you!
|
|
|
|
sandesh_moghe
Constraint Violating Yak Guru
India
310 Posts |
Posted - 09/27/2001 : 09:36:03
|
Oh Roy,
I was searching it for last 2 months.
Thanx a lot.
---------------------------
Sandesh - The Messanger |
|
|
|
robvolk
Most Valuable Yak
USA
15559 Posts |
Posted - 09/27/2001 : 10:16:05
|
Roy?
|
|
|
|
WallyE
Starting Member
2 Posts |
Posted - 10/25/2001 : 14:15:59
|
This solution seems to work great for me except for two small problems. I was wondering if you have seen this as well or if I am doing something incorrectly. The problems I am having are with the copy line that appends the root tags to the beginning and end. First, it doesn't seem to like me copying a file onto itself. I had to change the final file name to "myfile2.xml" for it to work. Not a problem and easy to work around, but just thought I'd share. The second issue is I seem to be getting a stray character at the end of the file resulting from the copy, as if the copy command puts some type of termination character there. Once I remove it, IE can render the XML just fine. I just need a way to get rid of the character! Any ideas?
Thanks,
Walter Enzor
Elliott Wave International |
|
|
|
robvolk
Most Valuable Yak
USA
15559 Posts |
Posted - 10/25/2001 : 15:08:57
|
It could be a Ctrl-Z character, some text editors (or using "copy con" on the command line) will put it in; it's the File Terminator character.
It'll be hard for you to tell if it's there. My best advice is to redo your root-open and root-close files, using NotePad, and make sure you only type the tags; don't add a carriage return.
It could be that the user account that ran the SP to copy the files might not have modify file permissions, or some other obscure permission. I had no trouble with an Admin-level user account.
|
|
|
|
WallyE
Starting Member
2 Posts |
Posted - 10/25/2001 : 15:22:53
|
Thanks for the quick reply! Just seconds ago, I got the copy to work by using the /B switch on the Copy command to force a binary copy into the file. That eliminated the character, but I'm still not sure what was adding it in there. I had rebuilt the file before in notepad, and that didn't seem to work, but after adding the switch, it looks great. The interesting thing is the character is displayed in Notepad as a box, so Notepad doesn't quite interpret it correctly, either. I added the switch for the source files and the destination file just to make sure I had my bases all covered. I'm running Win2K Svr SP2 in case I'm on a different OS and that is why I'm seeing it.
As far as the filecopy not copying over the existing file, I used a Trusted Connection and I have administrative rights, so I don't know what is going on there. I just created the first file with an extra ".tmp" extension on it and then deleted it after the copy. It works fine, and I don't have a problem setting it up that way. Thanks for creating a great solution!
|
|
|
|
AvdA
Starting Member
5 Posts |
Posted - 12/18/2001 : 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!
|
|
|
|
rksahu
Starting Member
2 Posts |
Posted - 04/09/2002 : 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 -t
Just 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 -t
copy rootopen.txt+myfile.xml+rootcls.txt myfile.xml
That will add the root tags and combine everything into one file.
|
|
|
|
rksahu
Starting Member
2 Posts |
Posted - 04/09/2002 : 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
|
|
|
|
robvolk
Most Valuable Yak
USA
15559 Posts |
Posted - 04/09/2002 : 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_mc
Starting Member
1 Posts |
Posted - 04/26/2002 : 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.
Thanks
senthil
|
|
|
|
robvolk
Most Valuable Yak
USA
15559 Posts |
Posted - 04/26/2002 : 12:25:56
|
Why not write the query as a stored procedure and use queryout "EXECUTE sp_name" in your bcp command?
|
|
|
|
mfemenel
Professor Frink
USA
1414 Posts |
Posted - 08/13/2002 : 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" |
|
|
|
darshansh
Starting Member
1 Posts |
Posted - 09/09/2002 : 02:51:34
|
Did you try passing the /B parameter at the end with the COPY command?
Also, see http://www.PerfectXML.com/Articles/XML/ExportSQLXML.asp for more details on exporting SQL data as XML.
-D
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"
|
|
|
|
tad
Starting Member
31 Posts |
Posted - 03/04/2003 : 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.
Thanks
drop procedure spXMLTest
go
create procedure spXMLTest
as
/*
Syntax: (From cmd prompt)
bcp "Exec Northwind.dbo.spXMLTest" queryout c:\test.xml -Szchq_mssql\prd1 -T -c -r -t
copy /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], Products
Where Customers.CustomerID = Orders.CustomerID
and Orders.OrderID = [Order Details].OrderID
and [Order Details].ProductID = Products.ProductID
and Customers.Country='Uk'
for XML AUTO
|
|
|
|
robvolk
Most Valuable Yak
USA
15559 Posts |
Posted - 03/04/2003 : 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.
|
|
|
|
tad
Starting Member
31 Posts |
Posted - 03/05/2003 : 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.
|
|
|
|
robvolk
Most Valuable Yak
USA
15559 Posts |
Posted - 03/05/2003 : 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 Fribble
Yak-finder General
United Kingdom
1961 Posts |
Posted - 03/05/2003 : 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 |
|
|
|
Topic |
|
SQL Server 2000 XML
