How to check whether a string is well-formed XML in SQL Server?

Question

I have a database with tables that have schemaless XML columns which contain arbitrary non-XML data (plain-text). Here a sample script to gerenate and fill such a table:

CREATE TABLE TestTable (
  ID INT NOT NULL IDENTITY (1, 1), 
  XmlColumn XML NOT NULL, 
  CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED (ID ASC) ON [PRIMARY]
) ON [PRIMARY]
GO

INSERT INTO TestTable (XmlColumn) VALUES ('<root><child /></root>');
INSERT INTO TestTable (XmlColumn) VALUES ('Foo, this is not XML');
INSERT INTO TestTable (XmlColumn) VALUES ('<root><parent><child /></parent></root>');
GO

How can I (preferred) enforce that only well-formed XML can be added? Or else, how can I determine which entries are not well-formed and NULL them out?

I have read several posts that suggest a CAST / CONVERT in conjunction with a TRY CATCH, (e.g. stackoverflow.com/questions/14753119), but I never get an exception, the CAST / CONVERT always succeeds:

DECLARE @xml AS XML;
DECLARE @isValid AS BIT = 1;
BEGIN TRY
  SET @xml = CONVERT(xml, 'Foo')
END TRY
BEGIN CATCH
  SET @isValid = 0;
END CATCH;
SELECT @isValid; -- returns 1

Any ideas?

question from:https://stackoverflow.com/questions/65617930/how-to-check-whether-a-string-is-well-formed-xml-in-sql-server

Answer 1

use tempdb
go

drop table if exists TestTable;
drop table if exists TestTablewithcheck;
drop function if exists dbo.mywellformedxml
go

CREATE TABLE TestTable (
  ID INT NOT NULL IDENTITY (1, 1), 
  XmlColumn XML NOT NULL, 
  CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED (ID ASC) ON [PRIMARY]
) ON [PRIMARY]
GO

INSERT INTO TestTable (XmlColumn) VALUES ('<root><child /></root>');
INSERT INTO TestTable (XmlColumn) VALUES ('Foo, this is not XML');
INSERT INTO TestTable (XmlColumn) VALUES ('<root><parent><child /></parent></root>');
INSERT INTO TestTable (XmlColumn) VALUES ('<root><child /></root>Foo, this is not XML');
INSERT INTO TestTable (XmlColumn) VALUES ('<root xmlns="test"><child /></root>');
INSERT INTO TestTable (XmlColumn) VALUES ('<!-- comment -->');
INSERT INTO TestTable (XmlColumn) VALUES ('<!-- comment --><root><child /></root>');
INSERT INTO TestTable (XmlColumn) VALUES ('<noroot><child /></noroot><noroot><child /></noroot>');
INSERT INTO TestTable (XmlColumn) VALUES ('<?pi my processing instruction?>');
GO

create or alter function dbo.mywellformedxml(@xml xml)
returns bit
with schemabinding
as
begin
    return
    ( 
        isnull(
        (
        select 1
        where @xml.exist('/*[1]') = 1 --root..
        and @xml.exist('/*[2]') = 0 --..only..
        and @xml.exist('text()') = 0 --..without text..
        ), 0)
    )
end
go

CREATE TABLE TestTablewithcheck (
  ID INT NOT NULL IDENTITY (1, 1), 
  XmlColumn XML NOT NULL, 
  CONSTRAINT [PK_TestTablewithcheck] PRIMARY KEY CLUSTERED (ID ASC) ON [PRIMARY],
  constraint chkwfxml check(dbo.mywellformedxml(XmlColumn) = 1)
) ON [PRIMARY]
GO

declare @i int = 1
while @i <= 9
begin
    insert into TestTablewithcheck(XmlColumn)
    select XmlColumn
    from TestTable
    where id = @i;
    
    select @i = @i + 1;
end
go

select *
from TestTablewithcheck;
go


select *, dbo.mywellformedxml(XmlColumn) as wfxml
from TestTable
go

drop table if exists TestTable;
drop table if exists TestTablewithcheck;
drop function if exists dbo.mywellformedxml
go