For XML - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

For XML

Author

Topic

stumbling
Posting Yak Master

104 Posts

Posted - 2009-03-01 : 20:26:49

Hi ALL

Hoping someone can help me i am looking to use the FOR XML statements in SQL 2000 however when i run it no matter what way the output seems to end at 257 characters and i dont know why?
Please help somebody

Cheers
Phil

Code to run on Northwind DB

SELECT Customers.CustomerID, Customers.ContactName,
Orders.ShipCity, Employees.FirstName + ' ' + Employees.LastName as
_EmployeeName, Employees.Region
FROM Customers INNER JOIN
Orders ON Customers.CustomerID = Orders.CustomerID INNER JOIN
Employees ON Orders.EmployeeID = Employees.EmployeeID
WHERE (Customers.CustomerID IN ('ALFKI', 'TRAIH'))
FOR XML AUTO

Output from Query.

<Customers CustomerID="ALFKI" ContactName="Maria Anders"><Orders ShipCity="Berlin" _EmployeeName="Michael Suyama"><Employees/></Orders><Orders ShipCity="Berlin" _EmployeeName="Margaret Peacock"><Employees Region="WA"/><Employees Region="WA"/></Orders><Orde

AS you can see it has stopped half way i would have expected something like this.
<Customers CustomerID="ALFKI" ContactName="Maria Anders">
<Orders ShipCity="Berlin" EmployeeName="Michael Suyama">
<Employees />
</Orders>
<Orders ShipCity="Berlin" EmployeeName="Margaret Peacock">
<Employees Region="WA" />
<Employees Region="WA" />
</Orders>
<Orders ShipCity="Berlin" EmployeeName="Nancy Davolio">
<Employees Region="WA" />
<Employees Region="WA" />
</Orders>
<Orders ShipCity="Berlin" EmployeeName="Janet Leverling">
<Employees Region="WA" />
</Orders>
</Customers>
<Customers CustomerID="TRAIH" ContactName="Helvetius Nagy">
<Orders ShipCity="Kirkland" EmployeeName="Margaret Peacock">
<Employees Region="WA" />
</Orders>
<Orders ShipCity="Kirkland" EmployeeName="Anne Dodsworth">
<e />
</Orders>
<Orders ShipCity="Kirkland" EmployeeName="Michael Suyama">
<Employees />
</Orders>
</Customers>

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-03 : 09:23:52

If you are using Query Analyzer, there is a limit of 256 characters for output.
Go to Tools -> Options to raise limit of reported number of characters.

E 12°55'05.63"
N 56°04'39.26"

stumbling
Posting Yak Master

104 Posts

Posted - 2009-03-04 : 16:51:36

Thanks for that i managed to work it out and you are 100% correct that was the issue. Much appreciated and sorry for the slow response

quote:
Originally posted by Peso

If you are using Query Analyzer, there is a limit of 256 characters for output.
Go to Tools -> Options to raise limit of reported number of characters.

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources