SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 SQL Server 2000 XML
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

jackstow
Posting Yak Master

United Kingdom
160 Posts

Posted - 09/25/2001 :  10:49:36  Show Profile  Visit jackstow's Homepage  Send jackstow a Yahoo! Message  Reply with Quote
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
15683 Posts

Posted - 09/25/2001 :  12:07:05  Show Profile  Visit robvolk's Homepage  Reply with 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/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
Go to Top of Page

jackstow
Posting Yak Master

United Kingdom
160 Posts

Posted - 09/27/2001 :  09:21:51  Show Profile  Visit jackstow's Homepage  Send jackstow a Yahoo! Message  Reply with Quote
Thank you!

Go to Top of Page

sandesh_moghe
Constraint Violating Yak Guru

India
310 Posts

Posted - 09/27/2001 :  09:36:03  Show Profile  Send sandesh_moghe an AOL message  Send sandesh_moghe a Yahoo! Message  Reply with Quote
Oh Roy,
I was searching it for last 2 months.
Thanx a lot.

---------------------------
Sandesh - The Messanger
Go to Top of Page

robvolk
Most Valuable Yak

USA
15683 Posts

Posted - 09/27/2001 :  10:16:05  Show Profile  Visit robvolk's Homepage  Reply with Quote
Roy?

Go to Top of Page

WallyE
Starting Member

2 Posts

Posted - 10/25/2001 :  14:15:59  Show Profile  Reply with Quote
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
Go to Top of Page

robvolk
Most Valuable Yak

USA
15683 Posts

Posted - 10/25/2001 :  15:08:57  Show Profile  Visit robvolk's Homepage  Reply with Quote
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.

Go to Top of Page

WallyE
Starting Member

2 Posts

Posted - 10/25/2001 :  15:22:53  Show Profile  Reply with Quote
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!

Go to Top of Page

AvdA
Starting Member

5 Posts

Posted - 12/18/2001 :  03:02:43  Show Profile  Reply with Quote
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!


Go to Top of Page

rksahu
Starting Member

2 Posts

Posted - 04/09/2002 :  15:17:38  Show Profile  Reply with Quote
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.





Go to Top of Page

rksahu
Starting Member

2 Posts

Posted - 04/09/2002 :  15:19:30  Show Profile  Reply with Quote
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

Go to Top of Page

robvolk
Most Valuable Yak

USA
15683 Posts

Posted - 04/09/2002 :  15:39:04  Show Profile  Visit robvolk's Homepage  Reply with Quote
What version of SQL Server/bcp are you using? Version 6.5 and earlier of bcp does not support the "queryout" parameter.

Go to Top of Page

senthil_mc
Starting Member

1 Posts

Posted - 04/26/2002 :  12:19:04  Show Profile  Reply with Quote
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


Go to Top of Page

robvolk
Most Valuable Yak

USA
15683 Posts

Posted - 04/26/2002 :  12:25:56  Show Profile  Visit robvolk's Homepage  Reply with Quote
Why not write the query as a stored procedure and use queryout "EXECUTE sp_name" in your bcp command?

Go to Top of Page

mfemenel
Professor Frink

USA
1421 Posts

Posted - 08/13/2002 :  13:45:56  Show Profile  Visit mfemenel's Homepage  Reply with 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"
Go to Top of Page

darshansh
Starting Member

1 Posts

Posted - 09/09/2002 :  02:51:34  Show Profile  Reply with Quote
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"



Go to Top of Page

tad
Starting Member

31 Posts

Posted - 03/04/2003 :  17:16:31  Show Profile  Reply with Quote
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
Go to Top of Page

robvolk
Most Valuable Yak

USA
15683 Posts

Posted - 03/04/2003 :  17:35:12  Show Profile  Visit robvolk's Homepage  Reply with Quote
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.

Go to Top of Page

tad
Starting Member

31 Posts

Posted - 03/05/2003 :  09:59:21  Show Profile  Reply with Quote
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.
Go to Top of Page

robvolk
Most Valuable Yak

USA
15683 Posts

Posted - 03/05/2003 :  10:18:51  Show Profile  Visit robvolk's Homepage  Reply with Quote
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)

Go to Top of Page

Arnold Fribble
Yak-finder General

United Kingdom
1961 Posts

Posted - 03/05/2003 :  12:14:48  Show Profile  Reply with Quote
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
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000