Oracle-SQL: Generate XML with single quote attributes-CodePudding

I'm building an XML in Oracle SQL.

I simply want to convert those double quotation marks to single quotation marks.

Is there an Oracle XML function or parameters that I can use to specify this format?

CodePudding user response：

You can use your existing code and then replace ' with ' and " with ':

SELECT REPLACE(
         REPLACE(
           your_xml,
           '''',
           '&apos;'
         ),
         '"',
         ''''
      ) AS single_quoted_xml
FROM  your_query

Which, for the sample data:

CREATE TABLE your_query (your_xml) AS
SELECT '<Test Test01="001" Test02="002">value</Test>' FROM DUAL UNION ALL
SELECT '<Test Test01="a ''quote''" Test02="another ''quote''">and more ''quotes'' here</Test>' FROM DUAL;

Outputs:

SINGLE_QUOTED_XML
<Test Test01='001' Test02='002'>value</Test>
<Test Test01='a 'quote'' Test02='another 'quote''>and more 'quotes' here</Test>

fiddle

CodePudding user response：

In Oracle SQL four (4) single quotes are represented as a (1) single quote, so you could just use Replace() function to do it:

WITH tst AS
        (Select '<Test Test01="001" Test02="002">Value</Test>' "MY_XML" From Dual)

Select MY_XML, Replace(MY_XML, '"', '''') "MY_XML_2" From tst

MY_XML                                       MY_XML_2                                   
-------------------------------------------- --------------------------------------------
<Test Test01="001" Test02="002">Value</Test> <Test Test01='001' Test02='002'>Value</Test>

Or in PL/SQL

SET SERVEROUTPUT ON
Declare
    mXML   VarChar2(50) := '<Test Test01="001" Test02="002">Value</Test>';
    sq     VarChar2(1) := '''';
Begin
    DBMS_OUTPUT.PUT_LINE(Replace(mXML, '"', sq));
End;
/

anonymous block completed
<Test Test01='001' Test02='002'>Value</Test>