CODEWITHSUNDEEP
xmloracle-databaseplsql
Felipe Vidal
Felipe Vidal

Reputation: 505

Oracle-SQL: Generate XML with single quote attributes

I'm building an XML in Oracle SQL.

enter image description here

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

enter image description here

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

Upvotes: 1

Views: 279

Answers (2)

d r
d r

Reputation: 7766

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>

Upvotes: 0

MT0
MT0

Reputation: 167822

You can use your existing code and then replace ' with &apos; 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 &apos;quote&apos;' Test02='another &apos;quote&apos;'>and more &apos;quotes&apos; here</Test>

fiddle

Upvotes: 1

Related Questions