October 15, 2013 at 2:27 pm
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
?
October 15, 2013 at 3:51 pm
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)?
October 16, 2013 at 7:43 am
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/>
<Three/>
so that each element in the column data be represented as an XML Element and not text in the element.
October 16, 2013 at 11:22 am
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.
October 16, 2013 at 3:27 pm
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)
October 17, 2013 at 11:30 am
thanks, this will do the job
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply