June 8, 2009 at 6:13 am
hi i am using sql 2000, i had written the stored procedure to export the data in XML format
CREATE Procedure Export_XML
(
@path varchar(100),@file varchar(100),@query varchar(1000)
)
As
Declare @bcp varchar(1000)
set @bcp='bcp'+' '+'"'+@query+'"'+' '+'queryout'+' '+@path+@file+' '+'-c -Usa -Psa -S'+ @@servername
print @bcp
EXEC master..xp_cmdshell @bcp
GO
--executing the procedure
Exec Export_XML 'c:\','test.XML','select * from sysobjects FOR XML AUTO, ELEMENTS'
the data gets export but when i try to open the XML file it gives me an error
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.
--------------------------------------------------------------------------------
Only one top level element is allowed in an XML document. Error processing resource 'file:///C:/test.xml'. Line 1, P...
can anyone tell me how to resolve the problem.
June 8, 2009 at 8:19 am
What do you get if you open the document in a simple word processor, and not an xml processor? Just right-click on it and choose "open with" and choose notepad. Take a look at the results and see if you can figure out what's wrong. First thing you'll probably see is that the XML isn't a fully formed XML document, but really an XML snippet. Manipulate the file in Notepad until it's in the format you want, then figure out how to make those changes in SQL, or just deal with the XML the way it is.
Sorry, not completely helpful, but really the XML implementation in SQL 2000 isn't fully formed in my experience.
Cheers,
Rick Todd
June 8, 2009 at 9:16 am
Well-Formed XML comes in two forms: The more expected "XML Document" and the less expected (but still "well-formed") "XML document fragment". The only difference is that XML documents have only one top-level element.
Most XML tools are intended to work with XML documents, but many will not work with fragments. SQL Server, however, happily works with and creates both. And the problem here is that your query "select * from sysobjects FOR XML AUTO, ELEMENTS" makes a fragment, NOT a document.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply