Xml – method in PL/SQL to convert/encode text to XML compliant text

oracleplsqlxml

I have a colleague who needs to convert text from a PL/SQL method into XML compliant text, as he is constructing a excel spreadsheet by updating a text template.

Is there a method in PL/SQL to convert/encode text to XML compliant text?

Best Answer

Well, if you just want to convert XML characters, you'll want to do something like...

  outgoing_text := DBMS_XMLGEN.CONVERT(incoming_text)

Where outgoing_text and incoming_text are both VARCHAR2 or CLOB.

You can specify a second argument, but it defaults to DBMS_XMLGEN.ENTITY_ENCODE... it can also decode XML entities by passing DBMS_XMLGEN.ENTITY_DECODE as a second argument.

Related Solutions

Sql-server – Encode xml column values as xml in sql server

Use FOR XML clause.

It can build XML from multiple values automatically:

WITH  q AS (
        SELECT  'Barnes & Noble' AS shop
        UNION ALL
        SELECT  'Marks & Spencer'
        )
SELECT  *
FROM    q
FOR XML AUTO, TYPE

---
<q shop="Barnes &amp; Noble" /><q shop="Marx &amp; Spencer" />

If you just want to encode an existing value, use:

SELECT  'Barnes & Noble'
FOR XML PATH('')

---
Barnes &amp; Noble

Sql – combination of “LIKE” and “IN” in SQL

There is no combination of LIKE & IN in SQL, much less in TSQL (SQL Server) or PLSQL (Oracle). Part of the reason for that is because Full Text Search (FTS) is the recommended alternative.

Both Oracle and SQL Server FTS implementations support the CONTAINS keyword, but the syntax is still slightly different:

Oracle:

WHERE CONTAINS(t.something, 'bla OR foo OR batz', 1) > 0

SQL Server:

WHERE CONTAINS(t.something, '"bla*" OR "foo*" OR "batz*"')

The column you are querying must be full-text indexed.

Reference: