We help IT Professionals succeed at work.

Export XML to file

Medium Priority
1,248 Views
Last Modified: 2013-11-19
I want to export data from a database to a XML-file. How can I do this using only T-SQL (with the FOR XML-clause), SQL-server 2000 and Windows 2000. This has to be a job within SQL-server.
I can write the select-statement, but I can't get it into a file. The data can be very long (more than 8000 characters).
Thanks.
Comment
Watch Question

CERTIFIED EXPERT

Commented:
you could execute it using


isql -S<server_name> -E -h-1 -Q "SET NOCOUNT ON GO <query>" -o <output_file>
. . . or set your query analyzer to export then results to a file . . .
CERTIFIED EXPERT

Commented:
erde, did it reach you?

Author

Commented:
To Miron,
It nearly works, but :
- after 2035 characters (independent of the -w argument), I have several lines with blank characters.
- with the -w argument < 2035, each line starts with a TAB-character.
Any solution?
CERTIFIED EXPERT

Commented:
erde, you lost me. XML by itself is okay with blank lines it does not break parser nor distorts the DOM document. Maybe I am just confused and do not understand what exactly blank lines look like ( seems like a good guess .)

I am also just started considering the look of the SELECT statement. If the select is hardcoded

select '<tag><element_name>' + str( column1 )....

then it might really break the XML under circumstatnces I can not envision. Please, paste the code and a place where output is broken.

Author

Commented:
Because it seems to be not so easy I have increased the points to 200.

The problem is that the result is split into lines of 2034 characters, followed by several lines with blanks, and the split happens into the elements. So this gives errors.

Executed into the query analyser it splits the result into lines of 2034 characters, but without lines with blanks.

I must say I use osql i.o. isql, because isql gives the error :
Msg 4004, Level 16, State 1, Server ERDE, Procedure spXML, Line 3
Unicode data in a Unicode-only collation or ntext data cannot be sent to clients using DB-Library (such as ISQL) or ODBC version 3.7 or earlier.

Here is the select statement :
SELECT
  1 AS Tag,
  NULL AS Parent,
  Address.lItemID AS [Customer!1!CustomerID!hide],
  Address.szName AS [Customer!1!CompanyName],
  NULL AS [Invoice!2!szInvoice],
  NULL AS [Invoice!2!dInvoice],
  NULL AS [InvoiceDetail!3!Description],
  NULL AS [InvoiceDetail!3!BaseVAT]
FROM Address
UNION ALL
-- 2. Second level of the hierarchy.
SELECT       
  2,
  1,
  Address.lItemID,
  Address.szName,
  InvoiceSwitch.szInvoice,
  InvoiceSwitch.dInvoice,
  NULL,
  NULL
FROM Address
JOIN InvoiceSwitch ON Address.lItemID = InvoiceSwitch.lCustomerID
UNION ALL
-- 3. Third level of the hierarchy.
SELECT  
  3,
  2,
  InvoiceSwitch.lCustomerID,
  NULL,
  InvoiceSwitch.szInvoice,
  NULL,
  InvoiceDetail.szDescription,
  InvoiceDetail.lfBaseVAT
FROM InvoiceSwitch
JOIN InvoiceDetail ON InvoiceSwitch.lItemID = InvoiceDetail.lItemID
ORDER BY
  [Customer!1!CustomerID!hide],
  [Invoice!2!szInvoice]
FOR XML EXPLICIT

And here is a part of the result (I only give the break because it's already long and I don't know what it will give into EE) :
BaseVAT="50.000000"/></Inv                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
 oice><Invoice szInvoice="2001-000009"
CERTIFIED EXPERT

Commented:
Nice use of On_line documentation samples. KB article Q307029 describes how to export data into XML file from query run against Access database. With all the background you already have it should be straighfoward how to adjust the detailes to work with SQL Server.

Thanks

Commented:
Use the undocumented feature DBCC traceon(257).

You can find more information about this problem into the column of Rich Rollman into SQL-server magazine (www.sqlmag.com), InstantDoc #20698.

Here is the text :
Why does Query Analyzer always truncate the output from a FOR XML query, regardless of whether you send the results to a file or the screen?
When SQL Server 2000 executes a FOR XML query, SQL Server returns a resultset that contains one text column and one or more rows. The column has a unique and unusual name: XML_F52E2B61-18A1-11d1-B105-00805F49916B (which I refer to here as the XML column). This special name lets the SQL Server OLE DB provider recognize the column as a stream of XML data; you can see this behavior easily in Query Analyzer by running a FOR XML query. SQL Server divides the XML result in the XML column into rows, where each row can contain up to 2033 characters. Constructing the XML stream from this XML resultset for each query would be very inconvenient. Thankfully, Microsoft extended both ADO and the SQL Server OLE DB provider to provide a stream of XML. The ADO and SQL Server OLE DB extensions let you return an XML query's results in an IStream or ADO Stream object instead of a standard ADO Recordset object or OLE DB rowset. ADO and OLE DB construct the stream by joining the rows of the XML column. (For details about the ADO and OLE DB XML extensions, see SQL Server Books Online-BOL.)
Now, let's look at how SQL Server's use of the XML column to return an XML result affects Query Analyzer's behavior. Surprisingly, Query Analyzer doesn't use the ADO or OLE DB extensions when executing a FOR XML query; Query Analyzer uses ODBC. And because Microsoft hasn't updated the ODBC driver to be XML-aware, the ODBC driver treats the data like any other text column. If you retrieve the results to a file or screen, the concatenation of multiple rows in the resultset causes extra line breaks. In addition to extra line breaks, data truncation can occur.
Truncation can occur when Query Analyzer's Maximum Characters per Column setting contains a value smaller than 2033. To fix the truncation problem, you can change this setting from its default value of 256 characters in Query Analyzer, Tools, Options, Results. This change solves the data-truncation problem, but line breaks still occur in the concatenation of data from two rows. The only way to avoid generating the line breaks is to use ADO, OLE DB, or an HTTP query and the SQL Server Internet Server API (ISAPI) DLL to obtain an XML result from SQL Server.
Another useful but undocumented feature can help you read the XML result that Query Analyzer displays. SQL Server supports using trace tags to alter the behavior of certain functions. If you set trace tag 257 on, SQL Server invokes a "pretty-print" algorithm on the XML output before returning it to make the XML result more readable. You can turn on the trace tag with the following statement:
DBCC traceon(257)
I frequently use the trace tag when coding examples for this column. I think you'll find the trace tag useful for obtaining more readable XML results when you use Query Analyzer for experimenting and prototyping.
CERTIFIED EXPERT

Commented:
I should apollogies for confusing your custom query and the sample in the Using Explicit Mode, once I saw the VAT field extension I realized my error. Still, I would encourage use of ADO and data stream provider over any other, even seemingly easier solution due to its much better support, it would certainly continue to be a mainstream of the things. And we want this things to work and be supported.
CERTIFIED EXPERT

Commented:
bcp has very little idea about XML and for every tiny change developer need to rework entire SQL statement + test it and manually test for errors that bcp will never catch. Considering usual data load through XML - more that 100 MB on average, the testing effort gets shifted on the shoulders of entire test team to test each new data load when data source adusts for a modification using front end - very clamcy and error prone practice. It is really cheaper and easier to use proper tools in proper place.

Author

Commented:
dewpat,
It works fine with the proposed BCP-parameters.

miron,
I don't see any advantages of isql (what you proposed) and bcp. Do you have another idea? I daily need to dump the data into a xml-file. I prefer to do it from within a sql-server job.

thanks.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.