CODEWITHSUNDEEP
sqlxmloracle-database
Oleksii
Oleksii

Reputation: 154

Check whether string is a well-formed xml

I have varchar2 column in DB. And this column stores string in xml format. But not all strings are well-formed xml (some with mistakes). How can I check is this string well-formed xml or not?

If this string isn't well formed xml, sql-query like this will fail at runtime:

   select 
       extractvalue(xmltype(some_table.value), 'Attachment/@category')
   from some_table

Xml has following format:

   <Attachment {attributes} />

Number of attributes in each string can be different.

So when the string like 'asdf' would occur my query wouldn't fail.

Upvotes: 3

Views: 9005

Answers (2)

Anshu
Anshu

Reputation: 7853

Oracle SQL function XMLIsValid and XMLType method IsSchemaValid()
Run the validation process unconditionally. Do not record any validation status. Returns:

  • 1 if the document is determined to be valid.
  • 0 if the document is determined to be invalid or the validity of the document cannot be determined.

XMLType method SchemaValidate()
Runs the validation process if the validation status is 0, which it is by default. Sets the validation status to 1 if the document is determined to be valid. (Otherwise, the status remains 0.)

XMLType method isSchemaValidated() returns the recorded validation status of an XMLType instance.

XMLType method setSchemaValidated() sets (records) the validation status of an XMLType instance.

Check this link for reference

Upvotes: 0

Joachim Isaksson
Joachim Isaksson

Reputation: 181077

Here's a simple function that will check it for you;

CREATE OR REPLACE FUNCTION isXML(xml CLOB)
RETURN NUMBER
AS
  xmldata XMLTYPE;
BEGIN
  xmldata := XMLTYPE(xml);
  return 1;
EXCEPTION
  when others then
    return 0;
END;
/

You can use it as;

SQL> SELECT isXML('fdjkasksdf') FROM DUAL;

ISXML('FDJKASKSDF')
-------------------
                  0

SQL> SELECT isXML('<body></body>') FROM DUAL;

ISXML('<BODY></BODY>')
----------------------
                     1

Upvotes: 7

Related Questions