5

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 -

  1. Create a table with 2 columns - ID, XML info for some product (non-typed).
  2. Add a row entry, that is, XML data for a product.
  3. Create an XML Schema for the XML-product-data.
  4. 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?

6

An XML Schema Collection for that particular piece of XML would look more like this:

CREATE XML SCHEMA COLLECTION XS_ProductDoc 
AS
'<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
    <xs:element name="Product">
        <xs:complexType mixed="true">
            <xs:sequence>
                <xs:element name="ProductID" type="xs:unsignedByte" />
                <xs:element name="ProductName" type="xs:string" />
                <xs:element name="SupplierID" type="xs:unsignedByte" />
                <xs:element name="CategoryID" type="xs:unsignedByte" />
                <xs:element name="QuantityPerUnit" type="xs:string" />
                <xs:element name="UnitPrice" type="xs:decimal" />
                <xs:element name="UnitsInStock" type="xs:unsignedByte" />
                <xs:element name="UnitsOnOrder" type="xs:unsignedByte" />
                <xs:element name="ReorderLevel" type="xs:unsignedByte" />
                <xs:element name="Discontinued" type="xs:unsignedByte" />
            </xs:sequence>
        </xs:complexType>
    </xs:element>
</xs:schema>'

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.

1
  • I understand that you've removed all the references, and this is more straightforward but I still don't get why is the Product not getting located? Isn't this Product == prod:Product? And prod namespace is also declared? I'm still confused, can you elaborate more, please?
    – PalashV
    Mar 30 '16 at 19:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.