SQL Server 2000 XML - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SQL Server 2000 XML

Author

Topic

jackstow
Posting Yak Master

160 Posts

Posted - 2001-09-25 : 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?

AvdA
Starting 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!

rksahu
Starting 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 -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 - 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

robvolk
Most 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_mc
Starting 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.

Thanks
senthil

robvolk
Most 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?

mfemenel
Professor 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"

darshansh
Starting Member

1 Post

Posted - 2002-09-09 : 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"

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

tad
Starting 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.

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

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.

tad
Starting 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.

robvolk
Most 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 Fribble
Yak-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

karanlik
Starting Member

2 Posts

Posted - 2003-10-09 : 09:53:37

Hi All,
I am trying to use the bcp utility to save the query results as XML file. But i am getting the following error. Can anyone offer a solution?
Thanks,

Bahtiyar KARANLIK
-----------------------
My bcp 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 -Sserver -Usa -Ppwd -C RAW -r -t'

Format File:

8.0
4
1 SQLCHAR 0 9 "\t" 1 CustID Turkish_CI_AS
2 SQLCHAR 0 100 "\t" 2 CustName Turkish_CI_AS
3 SQLCHAR 0 100 "\t" 3 CustSurname Turkish_CI_AS
4 SQLCHAR 0 100 "\t" 4 CustEmail Turkish_CI_AS

With the error :

Error = [Microsoft][ODBC SQL Server Driver]Host-file columns may be skipped only when copying into the Server

karanlik
Starting Member

2 Posts

Posted - 2003-10-09 : 10:17:00

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..
Thanks
Bahtiyar KARANLIK

bcp 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.0
4
1 SQLCHAR 0 9 "\t" 1 CustID Turkish_CI_AS
2 SQLCHAR 0 100 "\t" 2 CustName Turkish_CI_AS
3 SQLCHAR 0 100 "\t" 3 CustSurname Turkish_CI_AS
4 SQLCHAR 0 100 "\t" 4 CustEmail Turkish_CI_AS

Error String:
Error = [Microsoft][ODBC SQL Server Driver]Host-file columns may be skipped only when copying into the Server

Any 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:)

robvolk
Most 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.

jawg62
Starting 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 ##files
ORDER BY num
FOR XML EXPLICIT

The 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,
num
FROM ##files
ORDER BY [root!1!order!hide]
FOR XML EXPLICIT

It's a little hacky, but it writes a well formed xml document in one step.

Jake

emccarty
Starting Member

2 Posts

Posted - 2004-09-08 : 12:28:26

use pubs

create table root ( data char(1) NULL )

execute master..xp_cmdshell 'bcp "select top 10 * from pubs..root right outer join pubs..authors on 1 = 1 for xml auto" queryout \\stagingsql\Prod_bak\CC7335\bcp.xml -T -c -r -t'

/*
<pubs..root>
<pubs..authors au_id="172-32-1176" au_lname="White" au_fname="Johnson" phone="408 496-7223" address="10932 Bigge Rd." city="Menlo Park" state="CA" zip="94025" contract="1"/>
<pubs..authors au_id="213-46-8915" au_lname="Green" au_fname="Marjorie" phone="415 986-7020" address="309 63rd St. #411" city="Oakland" state="CA" zip="94618" contract="1"/>
<pubs..authors au_id="238-95-7766" au_lname="Carson" au_fname="Cheryl" phone="415 548-7723" address="589 Darwin Ln." city="Berkeley" state="CA" zip="94705" contract="1"/>
<pubs..authors au_id="267-41-2394" au_lname="O'Leary" au_fname="Michael" phone="408 286-2428" address="22 Cleveland Av. #14" city="San Jose" state="CA" zip="95128" contract="1"/>
<pubs..authors au_id="274-80-9391" au_lname="Straight" au_fname="Dean" phone="415 834-2919" address="5420 College Av." city="Oakland" state="CA" zip="94609" contract="1"/>
<pubs..authors au_id="341-22-1782" au_lname="Smith" au_fname="Meander" phone="913 843-0462" address="10 Mississippi Dr." city="Lawrence" state="KS" zip="66044" contract="0"/>
<pubs..authors au_id="409-56-7008" au_lname="Bennet" au_fname="Abraham" phone="415 658-9932" address="6223 Bateman St." city="Berkeley" state="CA" zip="94705" contract="1"/>
<pubs..authors au_id="427-17-2319" au_lname="Dull" au_fname="Ann" phone="415 836-7128" address="3410 Blonde St." city="Palo Alto" state="CA" zip="94301" contract="1"/>
<pubs..authors au_id="472-27-2349" au_lname="Gringlesby" au_fname="Burt" phone="707 938-6445" address="PO Box 792" city="Covelo" state="CA" zip="95428" contract="1"/>
<pubs..authors au_id="486-29-1786" au_lname="Locksley" au_fname="Charlene" phone="415 585-4620" address="18 Broadway Av." city="San Francisco" state="CA" zip="94130" contract="1"/>
</pubs..root>
*/

--Erik McCarty erik@texastar.com www.TexaStar.com

emccarty
Starting Member

2 Posts

Posted - 2004-09-08 : 12:33:07

use pubs
execute master..xp_cmdshell 'bcp "select top 10 * from ( select 1 root ) as root right outer join pubs..authors on 1 = 1 for xml auto" queryout c:\bcp.xml -T -c -r -t'

/*
<root root="1">
<pubs..authors au_id="172-32-1176" au_lname="White" au_fname="Johnson" phone="408 496-7223" address="10932 Bigge Rd." city="Menlo Park" state="CA" zip="94025" contract="1"/>
<pubs..authors au_id="213-46-8915" au_lname="Green" au_fname="Marjorie" phone="415 986-7020" address="309 63rd St. #411" city="Oakland" state="CA" zip="94618" contract="1"/>
<pubs..authors au_id="238-95-7766" au_lname="Carson" au_fname="Cheryl" phone="415 548-7723" address="589 Darwin Ln." city="Berkeley" state="CA" zip="94705" contract="1"/>
<pubs..authors au_id="267-41-2394" au_lname="O'Leary" au_fname="Michael" phone="408 286-2428" address="22 Cleveland Av. #14" city="San Jose" state="CA" zip="95128" contract="1"/>
<pubs..authors au_id="274-80-9391" au_lname="Straight" au_fname="Dean" phone="415 834-2919" address="5420 College Av." city="Oakland" state="CA" zip="94609" contract="1"/>
<pubs..authors au_id="341-22-1782" au_lname="Smith" au_fname="Meander" phone="913 843-0462" address="10 Mississippi Dr." city="Lawrence" state="KS" zip="66044" contract="0"/>
<pubs..authors au_id="409-56-7008" au_lname="Bennet" au_fname="Abraham" phone="415 658-9932" address="6223 Bateman St." city="Berkeley" state="CA" zip="94705" contract="1"/>
<pubs..authors au_id="427-17-2319" au_lname="Dull" au_fname="Ann" phone="415 836-7128" address="3410 Blonde St." city="Palo Alto" state="CA" zip="94301" contract="1"/>
<pubs..authors au_id="472-27-2349" au_lname="Gringlesby" au_fname="Burt" phone="707 938-6445" address="PO Box 792" city="Covelo" state="CA" zip="95428" contract="1"/>
<pubs..authors au_id="486-29-1786" au_lname="Locksley" au_fname="Charlene" phone="415 585-4620" address="18 Broadway Av." city="San Francisco" state="CA" zip="94130" contract="1"/>
</root>
*/

--Erik McCarty erik@texastar.com www.TexaStar.com

chrisgin
Starting Member

8 Posts

Posted - 2004-11-15 : 17:51:45

Hi,

I'm getting the following error when running the "bcp "SELECT * FROM pubs..authors FOR XML RAW" queryout c:\myfile.xml -Sserver -Uusername -Ppassword -c -r -t" command:

SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Warning: Server data (464 bytes) exceeds host-file field length (0 bytes) for field (1). Use prefix length, termination string, or a larger host-file field size. Truncation cannot occur for BCP output files.

Any ideas how to get around this?

Thanks,
Chris

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources