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 |
|
EliW
Starting Member
5 Posts |
 Posted - 2009-05-29 : 18:17:08
|
| Hi, newbie alert!! I'm well acquainted with T-SQL, but I've just been introduced to the miracle that is FOR XML PATH. The problem is, every query I run using FOR XML PATH just writes the XML in a long, continuous line, with no CR+LFs separating the elements OR the row results. At 1024 characters, the results get chopped off, then a new line starts and does the same thing again. I know how to extend the 1024 limit, but that would be impractical and wouldn't address the problem. How do I get the CR+LFs in there?Sample query:SELECT a, b, cFROM tablenameFOR XML PATH('item'), ROOT('root')Result:<root><item><a>a</a><b>b</b><c>c</c></item><item><a>a</a><b>b</b><c>c</c></item>....(no carriage returns or line feeds, ever)Thanks in advance for your help! |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-05-30 : 00:41:25
|
declare @string nvarchar(max), @correct xmlset @xml = (SELECT a, b, cFROM tablenameFOR XML PATH('item'), ROOT('root'))set @string = replace(replace(cast(@correct as nvarchar(max)), char(13), ''), char(10), '')But beware that this does also replace Cr and Lf inside the data, if present.
E 12°55'05.63"N 56°04'39.26" |
 |
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2009-05-30 : 01:07:24
|
| What are you doing with the XML? The limitations and presentation you are seeing are just in the query window, you shouldn't really be caring about the formatting of the XML, it's just data that a client application will display or manipulate as necessary. I'd strongly recommend that you don't try to format the XML in your query, you'll just introduce unnecessary overhead. |
|
|
|
EliW
Starting Member
5 Posts |
Posted - 2009-06-01 : 09:49:14
|
quote:
Originally posted by snSQL
What are you doing with the XML? The limitations and presentation you are seeing are just in the query window, you shouldn't really be caring about the formatting of the XML, it's just data that a client application will display or manipulate as necessary. I'd strongly recommend that you don't try to format the XML in your query, you'll just introduce unnecessary overhead.
No, the problems are not just in the query window. Regardless of whether I send results to Text, Grid, or File, I get lines that are 1024 characters long and then data is truncated off the end of every line. As an example, here is the tail end of one (1024 character) text line, and the start of the next, that I just produced by Sending to File:<startdate>07/01/2008</startdate><enddatification>Default ROP</classification>Because this is all I get back, I don't even know how much data is lost from one damaged row to the next; there may be rows' worth of lost data. I'd like formatted results, but I guess that's not important, so long as the data comes back intact.  I guess I also don't understand why this isn't just working right out-of-the-box. Every on-line resource I can find concerning FOR XML PATH makes no mention of this problem. |
|
|
|
EliW
Starting Member
5 Posts |
Posted - 2009-06-01 : 09:54:50
|
quote:
Originally posted by Peso
declare @string nvarchar(max), @correct xmlset @xml = (SELECT a, b, cFROM tablenameFOR XML PATH('item'), ROOT('root'))set @string = replace(replace(cast(@correct as nvarchar(max)), char(13), ''), char(10), '')But beware that this does also replace Cr and Lf inside the data, if present.
E 12°55'05.63"N 56°04'39.26"
I'm afraid this doesn't work, either. This statement:set @xml = (SELECT a, b, cFROM tablenameFOR XML PATH('item'), ROOT('root'))produces a single line of 1024 characters; all of the data past that point is lost. The query in question produces hundreds of records, which is why FOR XML PATH turned out to be such a Godsend, so I don't have to pick through it all and suppress any non-XML characters. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-06-01 : 10:02:22
|
No, the data is NOT lost. Maybe you are referring to PRINTING the data, then the data is truncated at character 1024 when displaying the data.The variable still holds evertything.See Tools -> Options -> Query Results
E 12°55'05.63"N 56°04'39.26" |
|
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2009-06-01 : 20:07:18
|
quote:
No, the problems are not just in the query window. Regardless of whether I send results to Text, Grid, or File, I get lines that are 1024 characters long
The point is it's not a problem of the data not being returned by SQL Server. The query is returning the data, but your client tool, the query window, your custom app or anything else has to use all of the data.You can set the SSMS to return longer values in Query Options, but ultimately you want to have your app handle this. Another thing - you posted this to the SS 2005 forum so the XML should open in the XML viewer when you click on it? |
|
|
|
ikik
Starting Member
1 Post |
Posted - 2009-09-08 : 18:25:10
|
Did you figure out the workaround for this problem?Thx!IKquote:
Originally posted by EliW
quote:
Originally posted by Peso
declare @string nvarchar(max), @correct xmlset @xml = (SELECT a, b, cFROM tablenameFOR XML PATH('item'), ROOT('root'))set @string = replace(replace(cast(@correct as nvarchar(max)), char(13), ''), char(10), '')But beware that this does also replace Cr and Lf inside the data, if present.
E 12°55'05.63"N 56°04'39.26"
I'm afraid this doesn't work, either. This statement:set @xml = (SELECT a, b, cFROM tablenameFOR XML PATH('item'), ROOT('root'))produces a single line of 1024 characters; all of the data past that point is lost. The query in question produces hundreds of records, which is why FOR XML PATH turned out to be such a Godsend, so I don't have to pick through it all and suppress any non-XML characters.
|
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-09-09 : 02:20:55
|
Goto Tools -> Options and increase amount of characters to be printed in query results.
N 56°04'39.26"E 12°55'05.63" |
|
|
|
|
|
|
|
|
No line feeds in FOR XML PATH