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
Previous Page
Author Previous Topic Topic Next Topic
Page: of 2

karanlik
Starting Member

2 Posts

Posted - 10/09/2003 :  09:53:37  Show Profile  Reply with Quote
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
Go to Top of Page

karanlik
Starting Member

2 Posts

Posted - 10/09/2003 :  10:17:00  Show Profile  Reply with Quote
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:)
Go to Top of Page

robvolk
Most Valuable Yak

USA
15635 Posts

Posted - 10/09/2003 :  13:03:06  Show Profile  Visit robvolk's Homepage  Reply with Quote
You cannot use a format file when outputting XML this way. XML is a serialized string and does not have columns.
Go to Top of Page

jawg62
Starting Member

1 Posts

Posted - 11/20/2003 :  06:59:57  Show Profile  Reply with Quote
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
Go to Top of Page

emccarty
Starting Member

2 Posts

Posted - 09/08/2004 :  12:28:26  Show Profile  Visit emccarty's Homepage  Reply with Quote
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
Go to Top of Page

emccarty
Starting Member

2 Posts

Posted - 09/08/2004 :  12:33:07  Show Profile  Visit emccarty's Homepage  Reply with Quote
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
Go to Top of Page

chrisgin
Starting Member

8 Posts

Posted - 11/15/2004 :  17:51:45  Show Profile  Reply with Quote
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
Go to Top of Page

James Birch
Starting Member

2 Posts

Posted - 04/08/2005 :  10:27:51  Show Profile  Reply with Quote
This is a warning message that you will always get if you set the line break character to nothing. If you are outputting XML (which is the only reason I can think of for setting the line break to nothing!) then you can safely ignore these warning messages. It simply indicates that the line length has gone over what the ODBC driver can normally handle (in the normal column / row output).

Personally, I'm still struggling with the lack of UTF-8 support in bcp...
Go to Top of Page

dj_sandro
Starting Member

1 Posts

Posted - 05/18/2005 :  15:31:28  Show Profile  Reply with Quote
Hello there! i'm new on this site! i was surfing around on the web looking for information how to connect MS Excel to MS SQL so i can access the stored procedure via XML maybe you guy's could help me out cause this is the only site with alot of sql info
Go to Top of Page

JimL
SQL Slinging Yak Ranger

USA
1534 Posts

Posted - 05/18/2005 :  15:36:34  Show Profile  Visit JimL's Homepage  Reply with Quote
dj_sandro
Excell is a spread sheet not a database, You could import a sheet into a SQL table but you could not "connect" them.
There have been a lot of posts on how to do this, use the search.

Jim
Users <> Logic
Go to Top of Page

spdhiva
Starting Member

13 Posts

Posted - 05/20/2008 :  15:44:21  Show Profile  Reply with Quote
Hi,

I have been doing some research on this XML file format for last 1 week and accidently got into this case. u guys rock!

it helps me in a bigtime. I was stugging with xml file format and unicode issue.

Thanks a lot.
Go to Top of Page

dmusson17
Starting Member

3 Posts

Posted - 07/25/2008 :  14:45:25  Show Profile  Click to see dmusson17's MSN Messenger address  Reply with Quote
How do I handle a select statement that has double quotes in it?
Go to Top of Page

BlueBeacon
Starting Member

India
3 Posts

Posted - 06/22/2011 :  03:32:46  Show Profile  Reply with Quote
Thanks for sharing us nice information.

Thanks & Regards
unspammed
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Previous 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.12 seconds. Powered By: Snitz Forums 2000