Use XML schema to verify XML data input

Currently, XML is increasingly used. In SQL Server tables, we can create XML columns to store data. Yesterday I saw someone in the Forum saying that a stored procedure was created to process XML, but an error was reported when the target table was inserted, but the error was not detailed. In fact, the root cause of this problem is that there is a problem with the XML data. We should verify the input data during insertion (the user input data must be verified ).

 

In fact, SQL Server has provided XML Schema verification. Here is an example:

 

-- Create XML Schema collation

Create XML
Schema collection mycollection
As

'<XSD: schemaxmlns: XSD = "http://www.w3.org/2001/XMLSchema"

Xmlns = "http: // mybooks"

Elementformdefault = "qualified"

Targetnamespace = "http: // mybooks">

<XSD: element name = "Bookstore" type = "bookstoretype"/>

<XSD: complextypename = "bookstoretype">

<XSD: sequencemaxoccurs = "unbounded">

<XSD: element name = "book" type = "booktype"/>

</XSD: sequence>

</XSD: complextype>

<XSD: complextype name = "booktype">

<XSD: sequence>

<XSD: element name = "title" type = "XSD: string"/>

<XSD: element name = "author" type = "authorname"/>

<XSD: element name = "price" type = "XSD: decimal"/>

</XSD: sequence>

<XSD: attribute name = "genre" type = "XSD: string"/>

<XSD: attributename = "publicationdate" type = "XSD: string"/>

<XSD: attribute name = "ISBN" type = "XSD: string"/>

</XSD: complextype>

<XSD: complextypename = "authorname">

<XSD: sequence>

<XSD: elementname = "first-name" type = "XSD: string"/>

<XSD: element name = "last-name" type = "XSD: string"/>

</XSD: sequence>

</XSD: complextype>

</XSD: schema>'

 

 

-- Create a table and use the XML schema created above for verification.

Create Table xmlcatalog (ID
Int, myinfoxml
(Content mycollection ));

 

-- Insert data

Insert xmlcatalogvalues (1, '<? Xmlversion = "1.0"?>

<Bookstorexmlns = "http: // mybooks">

<Book genre = "autobiography" publicationdate = "1981"

ISBN = "1-861003-11-0">

<Title> The Autobiography of specified infranklin </title>

<Author>

<First-name> Benjamin </first-name>

<Last-name> Franklin </last-name>

</Author>

<Price> 8.99 </price>

</Book>

<Book genre = "novel" publicationdate = "1967"

ISBN = "0-201-63361-2">

<Title> the confidenceman </title>

<Author>

<First-name> Herman </first-name>

<Last-name> Melville </last-name>

</Author>

<Price> 11.99 </price>

</Book>

<Book genre = "Philosophy" publicationdate = "1991"

ISBN = "1-861001-57-6">

<Title> the gorgias </title>

<Author>

<First-name> Sidas </first-name>

<Last-name> Plato </last-name>

</Author>

<Price> 9.99 </price>

</Book>

</Bookstore>

')

 

 

 

-- If the XML format is incorrect, an error is returned.

Insert xmlcatalogvalues (1, '<? Xmlversion = "1.0"?>

<Book genre = "Philosophy" publicationdate = "1991"

ISBN = "1-861001-57-6">

<Title> the gorgias </title>

<Author>

<First-name> Sidas </first-name>

<Last-name> Plato </last-name>

</Author>

<Price> 9.99 </price>

</Book>

</Bookstore>

')

 

 

MSG 6913, level 16, state 1, line 1

XML validation: Declaration not found for element 'book'. Location:/*: Book [1]

 

This error is very clear and can help us quickly troubleshooting.