How to keep HTML tags in FOR XML query

Question

How to keep HTML tags in FOR XML query

Lewis Corcoran 26

I need to upload a XML file with HTML code within it. I use the FOR XML PATH at the end of the query, the table has the relevant and tags, but when i click on my result these have been converted to their Unicode values as < and > How do i export these as the raw tags as %lt; is being recognised as the character < and > and not the   tag

Viorel 122.6K Reputation points

2023-04-12T12:50:45.63+00:00
Maybe remove the FOR XML PATH if it is not needed:

select XmlColumn from MyTable -- or: select cast(XmlColumn.query('/some/path') as nvarchar(max)) from MyTable

Or show details to reproduce the problem.
Yitzhak Khabinsky 26,586 Reputation points

2023-04-14T02:05:05.64+00:00

While asking a question you need to provide a minimal reproducible example: (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements. (2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL. (3) Desired output based on the sample data in the #1 above. (4) Your SQL Server version (SELECT @@version;)
Yitzhak Khabinsky 26,586 Reputation points

2023-04-14T02:13:16.29+00:00

While asking a question you need to provide a minimal reproducible example: (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements. (2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL. (3) Desired output based on the sample data in the #1 above. (4) Your SQL Server version (SELECT @@version;)
Anonymous

2023-04-14T08:55:37.6533333+00:00

Hi @Lewis Corcoran We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.
Anonymous

2023-04-21T08:37:03.77+00:00

Hi @Lewis Corcoran We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

3 answers

Your answer

Viorel 122.6K Reputation points

2023-04-12T12:50:45.63+00:00

Maybe remove the FOR XML PATH if it is not needed:

select XmlColumn from MyTable -- or: select cast(XmlColumn.query('/some/path') as nvarchar(max)) from MyTable

Or show details to reproduce the problem.
Yitzhak Khabinsky 26,586 Reputation points

2023-04-14T02:05:05.64+00:00

While asking a question you need to provide a minimal reproducible example: (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements. (2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL. (3) Desired output based on the sample data in the #1 above. (4) Your SQL Server version (SELECT @@version;)
Yitzhak Khabinsky 26,586 Reputation points

2023-04-14T02:13:16.29+00:00

While asking a question you need to provide a minimal reproducible example: (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements. (2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL. (3) Desired output based on the sample data in the #1 above. (4) Your SQL Server version (SELECT @@version;)
Anonymous

2023-04-14T08:55:37.6533333+00:00

Hi @Lewis Corcoran We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.
Anonymous

2023-04-21T08:37:03.77+00:00

Hi @Lewis Corcoran We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

Answer 1

Anonymous

Hi @Lewis Corcoran I'm not an expert on XML. Can this thread help you? https://stackoverflow.com/questions/10875577/how-do-you-keep-html-tags-when-querying-a-xml-using-sql Best regards, Percy Tang

Answer 2

Deleted

This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

Comments have been turned off. Learn more

Answer 3

Olaf Helper 47,436

HTML is nothing else then XML with predefined tags. You can not have XML code within XML code. The only way is to use a CDATA element: https://learn.microsoft.com/en-us/sql/relational-databases/xml/example-specifying-the-cdata-directive?view=sql-server-ver16

Share via

How to keep HTML tags in FOR XML query

3 answers

Your answer