Cast FOR XML to Varchar(max)

sql-server xml ssms for-xml
sixshift04 picture sixshift04 · Dec 19, 2012 · Viewed 13.7k times · Source

I have a query that returns XML which I want to convert to varchar. My query returns 93,643 characters of XML. When I try to cast my xml result as varchar, I only get 43,679 characters when I copy the result set to a text editor. When I do len(xmlString), I get 93,643 characters.

I know from this post that varchar(max) can have up to 2^31 characters and 1 byte = 1 character, but it seems to be cutting off my data.

Do XML characters count as more than 1 byte? Why am I not able to select all the data from my xml result?

CAST((SELECT COLUMNS FROM TABLE FOR XML PATH('Name'), TYPE) AS VARCHAR(MAX)

Answer

bummi picture bummi · Dec 19, 2012

This is just a limitation of the Managementstudio.
With a testquery on a bigger table I get described 43,679 characters.
The same Query deliveres 267089 characters in a application via ADO.

Related questions

How can I query a value in SQL Server XML column

I have following XML stored in a XML column (called Roles) in a SQL Server database. <root> <role>Alpha</role> <role>Beta</role> <role>Gamma</role> &…

Read More
Select values from XML field in SQL Server 2008

Just looking at my XML field, my rows look like this: <person><firstName>Jon</firstName><lastName>Johnson</lastName></person> <person><firstName>Kathy</firstName&…

Read More
How to query for Xml values and attributes from table in SQL Server?

I have a table that contains a Xml column: SELECT * FROM Sqm A sample of the xml data of a row would be: <Sqm version="1.2"> <Metrics> <Metric id="TransactionCleanupThread.RecordUsedTransactionShift" type="timer" unit="µs" count="1" …

Read More