BOL can not find using as [Text()] for xml.

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 2005 Forums

Transact-SQL (2005)

BOL can not find using as [Text()] for xml.

Author

Topic

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-05-24 : 11:17:55

A while back I believe peso posted a great trick when concatenating values using for XML.

The way it worked was when you did a query like so

select myfield as [text()]
from mytable a
for xml path('')

I can not find anywhere why text() removes the xml tags in BOL, and whether that is it's intended use or if this was just a trick that someone found out where it just works.

Can someone please point me to why this works.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-24 : 11:30:45

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

There are other methods to use besides text() trick.
One of the other methods also deals with entitization!

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

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2009-05-24 : 11:41:24

yeah bol really, really, really, really, really, really, really, really SUX when searching for xml related stuff.
text() is used to select the text node if there are nodes that are not text.
look here and search for text()
http://msdn.microsoft.com/en-us/library/ms345122.aspx

look at this example:


-- concats data after selecting only text data of the row
SELECT  City AS [text()]
FROM    Person.Address
FOR XML PATH('')

-- concats data in normal xml style, creates an xml item for each row data
SELECT  City
FROM    Person.Address
FOR XML PATH('')

-- we add the + ',' and it concats text data from the rows
SELECT  City +',' AS [text()]
FROM    Person.Address
FOR XML PATH('')

-- same as above. it just implicitly uses [text()] to concat row data
SELECT  City + ',' 
FROM    Person.Address
FOR XML PATH('')

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.5 out!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-24 : 12:09:33

Found it...

AS [text()]
AS [processing-instruction(query)]

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

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-05-24 : 12:20:08

Thanks, that ansers it!

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources