Sql-server – How to escape XML characters in TSQL before converting to XML

sql servert-sqltype conversionxml

I have XML structure which is input param for my stored procedure. It contains element with & (which is escaped in XML). When I extract that element to VARCHAR I receive & which is not valid XML character. I need to escape it, before converting to XML again. How to do that without REPLACE?

I have the following text: param1=xyz&para2=dasdasdfdas&param3. It is a part of query string. I converted it to XML and send it as a part of XML structure:

<zzz xmlns="http://example.com">
  <aaa>aaa</aaa>
  <bbb>param1=xyz&amp;para2=dasdasdfdas&amp;param3</bbb>
</zzz>

Inside the stored procedure I need to extract it. I do that with:

ISNULL(NULLIF(LTRIM(RTRIM(@XMLInput.value('declare default element namespace "http://example.com"; (zzz/bbb)[1]', 'NVARCHAR(250)'))), ''), '');

I after that the value contains normal text (&-s are not escaped – &).

After some processing I need to put that string inside other XML. I make:

CAST( ... AS XML);

Because &-s are not escaped I got error.

Best Answer

Use for xml path to create XML instead of casting.

select @YourVariable for xml path(''), type

Empty string in the path expression and the absence of an alias on the returned column will give you your string back as xml.

Related Solutions

Postgresql – How to escape XML field in XML-output query

The naive approach would be to wrap your query around another SELECT and use replace.

SELECT replace(
    (SELECT XMLAGG(data) FROM
         (SELECT XMLELEMENT(name item, XMLFOREST(
          title,
          body as description,
          date_created as date,
          'http://example.com/news'||id as link 
         )
     ) AS data FROM NEWS) AS smthng)::text, '<', '&lt;')

And do the same for >. A easier and more elgant mthod would be to to it with the language that should deliver/process/... the XML.

Mysql – Converting XML to MySQL tables

For MySQL, you might use use the ExtractData function.

If you are also using Windows (it needs Net Framework) then this old and outdated tool might still do the job.

http://xmltodb.sourceforge.net/

I have used it in the past and it converts the XML file to plain INSERT commands. For simple XML files it actually works. Of course the INSERT commmands will need some tweaking (change " or ') according to the db flavor but 90% of the actual job is done with the tool.

Commercial tools like XMLSpy http://www.altova.com/xmlspy/database-xml.html have a similar functionality (you may try the trial editions).

You can even try importing the file to MS Excel and then export to CSV for importing into the MySQL database. MS Excel has a nice feature XML maps that can map XML attributes and entities to columns. I tested it and it will easily work with a well formatted XML like yours.

Best Answer

Related Solutions

Postgresql – How to escape XML field in XML-output query

Mysql – Converting XML to MySQL tables

Related Question