Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

tsql for XML format the values as elements Expand / Collapse
Author
Message
Posted Tuesday, October 15, 2013 2:27 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 17, 2013 11:30 AM
Points: 8, Visits: 59
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
?
Post #1504970
Posted Tuesday, October 15, 2013 3:51 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 9:38 AM
Points: 6,908, Visits: 12,624
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
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1505011
Posted Wednesday, October 16, 2013 7:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 17, 2013 11:30 AM
Points: 8, Visits: 59
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.
Post #1505202
Posted Wednesday, October 16, 2013 11:22 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 9:38 AM
Points: 6,908, Visits: 12,624
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
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1505350
Posted Wednesday, October 16, 2013 3:27 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 8:29 AM
Points: 1,191, Visits: 2,022
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)

Post #1505429
Posted Thursday, October 17, 2013 11:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 17, 2013 11:30 AM
Points: 8, Visits: 59
thanks, this will do the job
Post #1505835
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse