We allow our developers to use XML columns to store data. We want to provide standard names to XML tags.
As a DBA, can we prevent the users from adding XML tags that are not predefined?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this communityYou could define a trigger in a suitable language to examine the data and block storing if validation errors occur. At least Postgres users could take this route.
For DB2 I found an example on how to use XML Schema for validation of XML columns. There should not a big difference to call XMLVALIDATE
from within a check constraint or a trigger.
The example basically proposes the following steps:
-- Import the XML schema
register xmlschema 'http://sample' from 'file:///sample.xsd' as sample_db_scheme.sample_xml_schema;
-- Put schema to repository
complete xmlschema sample_db_scheme.sample_xml_schema;
Once the schema was imported, you can use it to validate the XML:
-- Place this call in a trigger or constraint
XMLVALIDATE(? ACCORDING TO XMLSCHEMA ID sample_db_scheme.sample_xml_schema)