Solved

xml file from sql server using bcp

Posted on 2011-02-18
11
933 Views
Last Modified: 2012-05-11
I am trying to auto-generate xml file from sql server 2005 using bcp.
I have a stored procedure callled sp_Ap. I use "for xml auto, root("Iss"),elements" to get the required format.It generates the file fine.
My problem is when I try to generate it using bcp utility(I need to set it up to run automatically), it
only generates part of the file and error out in the middle.
This is what I used.
EXEC xp_cmdshell 'bcp "exec ErpLnX.dbo.sp_Ap" QUERYOUT \\va\users\L\Testing.xml -c -T'.

I checked the data and I don't see anything different on that line. Please help.

Errors getting :
---------------
The XML page cannot be displayed
Cannot view XML input using XSL style sheet. Please correct the error and then click the Refresh button, or try again later.


End tag 'Sales' does not match the start tag 'SalesOrder'. Error processing resource 'file:///C:/Documents and Settings/lpo...

-----------------------
0
Comment
Question by:MPI_IT
  • 5
  • 3
11 Comments
 
LVL 7

Expert Comment

by:lozzamoore
ID: 34944068
I've just tried a similar approach with a database I have here, across a very large table, and all works ok. How many rows are returned in your dataset?

It looks like the error you posted is actually when trying to open the file in an XML aware application that is complaining about the data being an XML fragment (which FOR XML AUTO produces), rather than a document.

Can you check if you actually get an error returned during the BCP call by running it manually?
Otherwise, open the XML in Notepad and see if it looks ok.

Hope that helps,

Regards,
0
 

Author Comment

by:MPI_IT
ID: 34945666
Maximum number of rows so far is around 100. Today morning, I tried when there was only one line and it worked fine. When I run the query now, result has 95 rows. It shows the following error
---
A name contained an invalid character. Error processing resource
---
In notepad, the structure is not shown properly. It is shown as one continuos string, no separate lines.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 34949037
>>Today morning, I tried when there was only one line and it worked fine. When I run the query now, result has 95 rows. It shows the following error<<
As alluded previously, it will be fine when only one row meets the condition, anything more and it will produce an Xml document that is not well-formed (it has no parent tag).

>>In notepad, the structure is not shown properly. It is shown as one continuos string, no separate lines. <<
Xml does not need "separate lines" for it to be well-formed or valid.
0
 
LVL 7

Expert Comment

by:lozzamoore
ID: 34950139
The error "a name contained an invalid character" sounds like it is an XML parsing error again, not the result of the BCP command.

Actually I suspect you are probably getting this error when selecting the XML in SQLServerManagement Studio, after you have run the FOR AUTO statement?

This is likely to be due to the column widths you have setup in SSMS.

See this link for more info:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=42573

We need to try and strip out all the other errors, and just focus on the error that BCP is producing please.

Thanks,
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

Author Comment

by:MPI_IT
ID: 34951976
I don't get any error while running in SQL server Managemet Studio using
exec sp_Ap ( which has select statement with for xml auto, root("Iss"),elements).
0
 
LVL 7

Expert Comment

by:lozzamoore
ID: 34959363
Test post as having trouble posting.
0
 
LVL 7

Expert Comment

by:lozzamoore
ID: 34959370
Ok so run the full thing again with its BCP and cmdshell wrapper and then open the file produced a non-XML aware editor like Wordpad or Ultraedit.

Does it look complete?
0
 

Accepted Solution

by:
MPI_IT earned 0 total points
ID: 34997779
When I used Type with xml auto, errors are gone and it looks like as valid xml file. I won't know it for sure until it is tested at a customer's computer.I want to close this case for now, and reopen if needed. Thanks for all the help.
0
 
LVL 7

Expert Comment

by:lozzamoore
ID: 35138879
The original issue reported seems to have been solved, so I would respectfully suggest that I have earned points for my advice.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to automate & schedule this Index optimization Script ? 10 76
Need help with a query 3 44
2 comma seperated list - SQL Server 12 36
passing parameter in sql procedure 9 56
This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This is a video describing the growing solar energy use in Utah. This is a topic that greatly interests me and so I decided to produce a video about it.

937 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

Need Help in Real-Time?

Connect with top rated Experts

5 Experts available now in Live!

Get 1:1 Help Now