Formatted output

Question

Post reply

Formatted output

Viewing 15 posts - 16 through 29 (of 29 total)

You must be logged in to reply to this topic. Login to reply

James_R_Alves SSC Veteran Points: 259 More actions · Answer 1

Sometimes my skills as a SQL parser could be a bit more...thorough.

I missed the leading "N" in DepartmentName's definition.

Your explanation makes great sense--now knowing that it was NVARCHAR makes all the difference.

Thanks, Brian.

James_R_Alves SSC Veteran Points: 259 More actions · Answer 2

Ah. Another human SQL parser error. The text() as a red herring alias really worked well.

Thanks, Raul.

NBSteve SSCarpal Tunnel Points: 4055 More actions · Answer 3

raulggonzalez (10/29/2014)
[text()] is a misleading alias in this case... Note that is wrapped with [square brackets]
just run
SELECT
DepartmentName + ';' AS [text()]
FROM
Department

While [text()] is an alias, the specific use of "[text()]" is critical to making this work. I'm not too familiar with the details, but I believe aliasing the column as text() tells the FOR XML function to put the column contents as plain text, rather than wrapping them in an entity tag.

Notice the difference between:

SELECT 'abcdefg' AS [text()]

FOR XML PATH('')

which uses the text() function to suppress the element xml tags, compared to

SELECT 'abcdefg' AS [text]

FOR XML PATH('')

which just uses the alias of "text" rather than the function name, and gets <text> tags included in the final XML.

keith.fearnley Mr or Mrs. 500 Points: 587 More actions · Answer 4

Thanks for the question, though I agree that STUFF is simpler.

I also wanted to add that if you may have odd characters in your text, you will fall foul of FOR XML expanding them.

For example if one of the departments was 'Research & development', the output would include "& amp;" (without the space) instead of the "&" sign, which is probably not what you'd want.

So instead of...

-- earlier part of statement

(

SELECT ...

FOR XML PATH('')

)

-- following part of statement

... you can use the following, which will preserve XML special characters...

-- earlier part of statement

(

SELECT ...

FOR XML PATH(''), type

).value('.', 'varchar(max)')

-- following part of statement

Note that this works if you are using the (SELECT... FOR XML) as a column in the SELECT as in the following code, but doesn't if you are using it as a pseudo-table as in the original structure.

So this form works...

SELECT STUFF(

(SELECT ';' + DepartmentName

FROM Department

FOR XML PATH(''), type

).value('.', 'varchar(max)'), 1, 1, '')

... but I'm not sure of the syntax necessary to get it to work in the format of the original example (I'm missing something, probably a brain). I can get it to work with another nested SELECT but that's madness. I usually opt for the inline select-as-a-column approach.

I got this helpful information from this blog post (grateful hat-tip)...

http://sqlblog.com/blogs/rob_farley/archive/2010/04/15/handling-special-characters-with-for-xml-path.aspx

Hope it helps someone.

keith.fearnley Mr or Mrs. 500 Points: 587 More actions · Answer 5

The form that preserves ampersands in the approach originally used would be... (notice the required additional nested SELECT). This is also explained in the replies and responses to the blog post noted above, from which I got the original solution.

SELECT

SUBSTRING(Ms, 1, DATALENGTH(Ms) / 2 - 1)

FROM

(SELECT

( SELECT DepartmentName + ';'

FROM Department

FOR XML PATH(''), type

).value('.', 'nvarchar(100)')

) AS T ( Ms )

James_R_Alves SSC Veteran Points: 259 More actions · Answer 6

What a great hack! It solves a problem that I've had shelved for some time now.

Thanks, Keith!

SQLDoubleG Hall of Fame Points: 3100 More actions · Answer 7

NBSteve (10/29/2014)
raulggonzalez (10/29/2014)
[text()] is a misleading alias in this case... Note that is wrapped with [square brackets]
just run
SELECT
DepartmentName + ';' AS [text()]
FROM
Department
While [text()] is an alias, the specific use of "[text()]" is critical to making this work. I'm not too familiar with the details, but I believe aliasing the column as text() tells the FOR XML function to put the column contents as plain text, rather than wrapping them in an entity tag.
Notice the difference between:
SELECT 'abcdefg' AS [text()]
FOR XML PATH('')
which uses the text() function to suppress the element xml tags, compared to
SELECT 'abcdefg' AS [text]
FOR XML PATH('')
which just uses the alias of "text" rather than the function name, and gets <text> tags included in the final XML.

True, that's why I avoid naming the result column when combining STUFF and XML PATH('')...

Edit: these kind of things you do and because it works you don't dig much.

Now I was curious about it and read "Columns with the Name of an XPath Node Test" from BOL

Marcia J SSCertifiable Points: 5649 More actions · Answer 8

Thanks for the question and the comments. I have learned a lot from both today.

serg-52 SSCrazy Eights Points: 9913 More actions · Answer 9

Nice question.

raulggonzalez (10/29/2014)
The reason why uses DATALENGTH is because the column is DEFINED as NVARCHAR()

Really, it doesn't matter because SQL Server stores XML data in Unicode (UTF-16) http://msdn.microsoft.com/en-us/library/bb522655(v=sql.100).aspx

So FOR XML output is always NVARCHAR.

Also note AS [text()] may be omitted just leaving expression DepartmentName + ';' with no alias. Missing expression alias prevents xml tag generation, same as Text().

Bangla Hall of Fame Points: 3137 More actions · Answer 10

Bangla

Hall of Fame

Points: 3137

October 30, 2014 at 3:38 am

#1755719

Nice question...

TomThomson SSC Guru Points: 104773 More actions · Answer 11

Nice question, but why does the explanation say "first" instead of "last" when the last character is removed by SUBSTRING(Ms, 1, DATALENGTH(Ms) / 2 - 1) and the first character is not removed?

Tom