Use XML schema to verify XML data input

Source: Internet
Author: User
Tags create xml schema

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.

 

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.