tsql for XML format the values as elements

Question

Post reply

tsql for XML format the values as elements

isingh

Old Hand

Points: 370
More actions
October 15, 2013 at 2:27 pm

#395672

I am trying to get the values in a column as XML elements. Is it possible to do this using For XML in sql server?
declare @XMLTest table( [Name] [nvarchar](50) NOT NULL )
INSERT @XMLTest ([Name]) VALUES (N'One¬d¦Uº')
INSERT @XMLTest ([Name]) VALUES (N'Two')
INSERT @XMLTest([Name]) VALUES (N'Three')
I would like to get the following on separate rows from the select query.
This would help me escape the Invalid characters in the values, so they can then be serialized to XML properly.
<One_x00AC_d_x00A6_U_x00BA_>
<Two/>
<Three/>
Is it possible to get this return from the FOR XML query, so that the invalid characters are properly encoded when the sql server generates the XML
?

Viewing 6 posts - 1 through 5 (of 5 total)

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

I'm not sure why those character wouldn't be properly displayed.

I tried

select NAME as a from @XMLTest for XML PATH('')

and received

<a>One¬d¦Uº</a>

<a>Two</a>

<a>Three</a>

So I don't think there's any need to replace thoses special character.

What exact output are you looking for (especially in terms of tag names)?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

isingh Old Hand Points: 370 More actions · Answer 2

Thanks for your reply Lutz, but as you can see in my post, I want the output to be

<One_x00AC_d_x00A6_U_x00BA_>

<Two/>

so that each element in the column data be represented as an XML Element and not text in the element.

LutzM SSC Guru Points: 107049 More actions · Answer 3

I don't understand why values of a column would need to be translated into tag (or element) names without any text value, especially if the values contain invalid character for xml elements. But there might be reasons out there to do so...

I'm out.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

sestell1 SSChampion Points: 10230 More actions · Answer 4

Here's a horrible and almost completely useless solution, but it does work... although it doesn't return the elements in the shortened notation....

DECLARE @XMLTest TABLE( [Name] [NVarChar](50) NOT NULL )

DECLARE @SQL AS VarChar(500)

INSERT @XMLTest ([Name]) VALUES (N'One¬d¦Uº')

INSERT @XMLTest ([Name]) VALUES (N'Two')

INSERT @XMLTest([Name]) VALUES (N'Three')

SELECT

@SQL = 'SELECT [' +

Max(CASE Ranking WHEN 1 THEN Name ELSE Null END) + '] = '''', [' +

Max(CASE Ranking WHEN 2 THEN Name ELSE Null END) + '] = '''', [' +

Max(CASE Ranking WHEN 3 THEN Name ELSE Null END) + '] = ''''' +

'FOR XML RAW(''''), ELEMENTS'

FROM

(

SELECT

ROW_NUMBER() OVER(ORDER BY Name) AS Ranking,

Name

FROM

@XMLTest

) X

EXEC (@SQL)

isingh Old Hand Points: 370 More actions · Answer 5

isingh

Old Hand

Points: 370

October 17, 2013 at 11:30 am

#1659429

thanks, this will do the job