Skip to content

Instantly share code, notes, and snippets.

@tmuth

tmuth/orcl-1-row-json.sql

Created June 26, 2017 21:47
Show Gist options
  • Save tmuth/455f6a1d2bf2869f3dc881d9e8f86851 to your computer and use it in GitHub Desktop.
Save tmuth/455f6a1d2bf2869f3dc881d9e8f86851 to your computer and use it in GitHub Desktop.
Download ZIP
Oracle get 1 row as JSON
Raw
orcl-1-row-json.sql
SELECT regexp_replace(xml3,'(.+),.+$','{\1}',1,1,'n') xml4
FROM
(SELECT regexp_replace(xml2,'<(\w+)>(.+)</\1>','"\1" : "\2",',1,0,'n') xml3
FROM
(SELECT regexp_replace(xml,'(^.+<ROW>)(.*)(</ROW>.*)','\2',1,1,'n') xml2
FROM
(SELECT dbms_xmlgen.getXML('SELECT * from soe.customers where rownum=1') xml
FROM dual
)
)
);
@tmuth
Copy link
Author

tmuth commented Jun 26, 2017

Output:
{ "CUSTOMER_ID" : "50865", "CUST_FIRST_NAME" : "marco", "CUST_LAST_NAME" : "hamilton", "NLS_LANGUAGE" : "EG", "NLS_TERRITORY" : "Belgium", "CREDIT_LIMIT" : "5000", "CUST_EMAIL" : "freddie.valdez@hotmail.com", "ACCOUNT_MGR_ID" : "389", "CUSTOMER_SINCE" : "25-MAR-04", "CUSTOMER_CLASS" : "Occasional", "SUGGESTIONS" : "Health", "DOB" : "17-MAY-94", "MAILSHOT" : "Y", "PARTNER_MAILSHOT" : "Y", "PREFERRED_ADDRESS" : "190107", "PREFERRED_CARD" : "137430"}

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment