Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-09-04 : 11:21:18
|
| Ben writes "I am trying to export a query to XML following the SQLXML Trans-SQL syntax...I have the following SELECT statement:SELECT 1 as Tag, NULL as Parent, Customers.CustomerID as [Customer!1!CustomerID], NULL as [Order!2!OrderID]FROM CustomersUNION ALLSELECT 2, 1, Customers.CustomerID, Orders.OrderIDFROM Customers, OrdersWHERE Customers.CustomerID = Orders.CustomerIDORDER BY [Customer!1!CustomerID], [Order!2!OrderID]FOR XML EXPLICITYou might recognize this from the MS Example for Using EXPLICIT Mode...Here's the problem, the output is not well-formed XML. In fact, it generates incomplete duplicate elements depending upon the row that it limited out on.Here's an example of the first row's output:<Customer CustomerID="ALFKI"><Order OrderID="10643"/><Order OrderID="10692"/><Order OrderID="10702"/><Order OrderID="10835"/><Order OrderID="10952"/><Order OrderID="11011"/></Customer><Customer CustomerID="ANATR"><Order OrderID="10308"/><Order OrderID="106As you can see it is truncating that row and produces badly formatted XML file, too...Am I missing some configuration set up with SQL Server that enables this Trans-SQL option?TIA,Ben CrameriPath Solutions" |
|
|
jasper_smith
SQL Server MVP & SQLTeam MVY
846 Posts |
Posted - 2002-09-04 : 15:12:14
|
| I assume you mean truncating in query analyser ?Two things1) place dbcc traceon(257) before the statement and return results in text view. This trace flag makes the xml look nice.2) In QA>Tools>Options>Results increase the maximum characters per columnHTHJasper Smith |
 |
|
|
|
|
|
FOR XML Output Not Well Formed XML