Solved

For XML output is missing at the end of the line.

Posted on 2003-11-28
12
517 Views
Last Modified: 2013-11-19
I did a "select * from dbo.Employees FOR XML AUTO, XMLDATA"
The output of each line is incomplete, so when I output to file is not a good XML doc. Help!!!

Thanks.

Jammy

--Sample output
<Schema name="Schema6" xmlns="urn:schemas-microsoft-com:xml-data" xmlns:dt="urn:schemas-microsoft-com:datatypes"><ElementType name="dbo.Employees" content="empty" model="closed"><AttributeType name="EmployeeID" dt:type="i4"/><AttributeType name="LastName"
 Colorado State University in 1970.  She also completed &quot;The Art of the Cold Call.&quot;  Nancy is a member of Toastmasters International." ReportsTo="2" PhotoPath="http://accweb/emmployees/davolio.bmp"/><dbo.Employees xmlns="x-schema:#Schema6" Employ
Address="4110 Old Redmond Rd." City="Redmond" Region="WA" PostalCode="98052" Country="USA" HomePhone="(206) 555-8122" Extension="5176" Photo="dbobject/dbo.Employees[@EmployeeID='4']/@Photo" Notes="Margaret holds a BA in English literature from Concordia Co
es Professional.&quot;  He is fluent in Japanese and can read and write French, Portuguese, and Spanish." ReportsTo="5" PhotoPath="http://accweb/emmployees/davolio.bmp"/><dbo.Employees xmlns="x-schema:#Schema6" EmployeeID="7" LastName="King" FirstName="Rob
erman." ReportsTo="5" PhotoPath="http://accweb/emmployees/davolio.bmp"/>
0
Comment
Question by:jlislo
  • 5
  • 5
12 Comments
 
LVL 9

Expert Comment

by:miron
ID: 9839921
did you try

bcp "select * from dbo.Employees FOR XML AUTO, XMLDATA" queryout c:\test.xml -T -S <servername>

or

bcp "select * from dbo.Employees FOR XML AUTO, XMLDATA" queryout c:\test.xml -S <servername> -U <user_name> -P <'password'>

do not hesitate to post if you meet any difficulties.

--cheers
0
 

Author Comment

by:jlislo
ID: 9840058
I try

bcp "select * from dbo.Employees FOR XML AUTO, XMLDATA" queryout "c:\test.xml" -S"Myserver" -U"sa" -P"sa"

I got

Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near 'queryout'.

Any idea?

Thanks

0
 
LVL 9

Expert Comment

by:miron
ID: 9840150
how about this one
bcp "select * from Northwind.dbo.Employees FOR XML AUTO, XMLDATA" queryout  c:\test.xml  -c -S  Myserver -U sa -P sa
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 9

Expert Comment

by:miron
ID: 9840415
also, post result of the bcp version query

bcp -v

--cheers
0
 
LVL 9

Expert Comment

by:miron
ID: 9840928
jlislo,

any progress...

--cheers
0
 

Author Comment

by:jlislo
ID: 9844383
Same error

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'queryout'.

Something is missing here.
0
 

Author Comment

by:jlislo
ID: 9844433
I think we are off track now. I try to find out why the output is incomplete (broken XML tags) Thanks

 
0
 
LVL 9

Expert Comment

by:miron
ID: 9846388
this should help

C:\>echo.^</root^>>> c:\test1.xml
C:\>type C:\test.xml >> c:\test1.xml
C:\>echo.^<root^>> c:\test1.xml
C:\>bcp "select * from Northwind.dbo.Employees FOR XML AUTO, XMLDATA" queryout  c:\test.xml  -c -S  Myserver -U sa -P sa

--cheers
0
 

Author Comment

by:jlislo
ID: 9847272
Miron,

Thank you for your help. Again my goal is to find out "Why the output is incomplete (broken XML tags)?" Output to text is not the key.

0
 

Author Comment

by:jlislo
ID: 9847606
Please read "SELECT...FOR XML AUTO Results Are Separated into Lines of 2033 Chars with ODBC" This article was previously published under Q275583

http://support.microsoft.com/default.aspx?scid=kb;en-us;275583&Product=sql2k

I think I found the answer
0
 
LVL 2

Accepted Solution

by:
Lunchy earned 0 total points
ID: 9885929
PAQed, with points refunded (125)

Lunchy
Friendly Neighbourhood Community Support Admin
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

JavaScript has plenty of pieces of code people often just copy/paste from somewhere but never quite fully understand. Self-Executing functions are just one good example that I'll try to demystify here.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Viewers will learn how the fundamental information of how to create a table.
The viewer will receive an overview of the basics of CSS showing inline styles. In the head tags set up your style tags: (CODE) Reference the nav tag and set your properties.: (CODE) Set the reference for the UL element and styles for it to ensu…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question