I'm new to SQL Server and I have this task to complete (for learning purposes), yet I'm unable to figure out this error due to inexperience with XML Schemas (I think)
Here's what I'm trying to do –
- Create a table with 2 columns – ID, XML info for some product (non-typed).
- Add a row entry, that is, XML data for a product.
- Create an XML Schema for the XML-product-data.
- Bind this schema to the one (column) in the table.
I did this similar to what is mentioned here.
-- Create table ProductDocs
CREATE TABLE ProductDocs
(
ID INT IDENTITY PRIMARY KEY,
ProductDoc XML NOT NULL
);
-- Insert data into ProductDocs
INSERT INTO ProductDocs
(ProductDoc)
VALUES ('<Product>
<ProductID>1</ProductID>
<ProductName>Chai</ProductName>
<SupplierID>1</SupplierID>
<CategoryID>1</CategoryID>
<QuantityPerUnit>10 boxes x 20 bags</QuantityPerUnit>\
<UnitPrice>18.0000</UnitPrice>
<UnitsInStock>39</UnitsInStock>
<UnitsOnOrder>0</UnitsOnOrder>
<ReorderLevel>10</ReorderLevel>
<Discontinued>0</Discontinued>
</Product>');
-- Create XML Schema
CREATE XML SCHEMA COLLECTION XS_ProductDoc
AS '<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
targetNamespace="http://www.microsoft.com/schemas/adventure-works/products"
xmlns:prod="http://www.microsoft.com/schemas/adventure-works/products">
<xs:element name="Product">
<xs:complexType>
<xs:sequence>
<xs:element ref="prod:ProductID" />
<xs:element ref="prod:ProductName" />
<xs:element ref="prod:SupplierID" />
<xs:element ref="prod:CategoryID" />
<xs:element ref="prod:QuantityPerUnit" />
<xs:element ref="prod:UnitPrice" />
<xs:element ref="prod:UnitsInStock" />
<xs:element ref="prod:UnitsOnOrder" />
<xs:element ref="prod:ReorderLevel" />
<xs:element ref="prod:Discontinued" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="ProductID" type="xs:integer" />
<xs:element name="ProductName" type="xs:string" />
<xs:element name="SupplierID" type="xs:integer" />
<xs:element name="CategoryID" type="xs:integer" />
<xs:element name="QuantityPerUnit" type="xs:string" />
<xs:element name="UnitPrice" type="xs:double" />
<xs:element name="UnitsInStock" type="xs:integer" />
<xs:element name="UnitsOnOrder" type="xs:integer" />
<xs:element name="ReorderLevel" type="xs:integer" />
<xs:element name="Discontinued" type="xs:boolean" />
</xs:schema>';
-- Bind the schema
ALTER TABLE ProductDocs
ALTER COLUMN ProductDoc xml (XS_ProductDoc);
But I'm getting this error –
XML Validation: Declaration not found for element 'Product'. Location:
/*:Product[1] The statement has been terminated.
Why is this error occurring?
And, how can I resolve it?
Best Answer
An XML Schema Collection for that particular piece of XML would look more like this:
The reason your code does not work is because your XML does not contain any namespaces, whereas the original AdventureWorks XML does. Learn more about XML Namespaces here.