CODEWITHSUNDEEP
sqlxmloracle-databaseplsqloracle-sqldeveloper
Parsu
Parsu

Reputation: 83

Need help in converting and validating XMLType in Oracle SQL Table

So the scenario here is data is coming from an external system to oracle database. I need to convert CLOB datatype to XMLTYPE and validate the XMLTYPE field in a table. If the validation or conversion fails, i need to push this record in another table. How can i achieve this.

I tried using XMLTYPE function and XMLISVALID function but i keep getting errors.

Upvotes: 1

Views: 407

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175924

You could use WITH FUNCTION:

CREATE TABLE t(id INT, x CLOB);
INSERT INTO t(id, x) VALUES(1, '<root/>');
INSERT INTO t(id, x) VALUES(2, '<root></root>');
INSERT INTO t(id, x) VALUES(3, '<root><a></root>'); -- incorrect XML

Checking if content of column is valid XML:

WITH FUNCTION func(col CLOB) RETURN NUMBER
AS
  x XMLTYPE;
BEGIN
   x := XMLTYPE(col);
   RETURN 1;

   EXCEPTION WHEN OTHERS THEN
   RETURN 0;
END;
SELECT *
FROM t
WHERE func(x) = 1;  -- get only valid XML
-- WHERE func(x) = 0; -- get invalid XML

db<>fiddle demo

Upvotes: 2

Related Questions