Solved

msg213 error when trying to insert data

Posted on 2012-03-27
5
390 Views
Last Modified: 2012-04-08
Hello!

I am trying to learn more about xml and sql-server. I have a copy of the book Sql server 2005 express edition for dummies from which I try to follow the exact instructions. I have not found any information on the books website that anything is wrong so assume I  missunderstand some detail.

The problem (Msg 213, Level 16, State 1, Line 1
Column name or number of supplied values does not match table definition.
)
 arise when I try to execute the following command in MS sql server management studio:

INSERT INTO Shipments VALUES(1,'<Root>
<Parts OrderID="1" PurchaseOrderNumber="NSSDJS#1">
<DeliveryDate>2006-12-30</DeliveryDate>
<Items>
   <ShipmentWeight>2.44</ShipmentWeight>
     <LineItem>
	        <Name>DentaKit for Adults</Name>
	        <Code>DK-R001</Code>
	        <Price>29.95</Price>
     </LineItem>
     <LineItem>
	        <Name>Retainer Brite 1 Year</Name>
	        <Code>DK-RB1Y</Code>
	        <Price>35.00</Price>
     </LineItem>
</Items>
</Parts>
</Root>')
GO

Open in new window


Before, I have used these statements to create xm-schema and a table.

CREATE XML SCHEMA COLLECTION Parts AS
N'<?xml version="1.0" encoding="UTF-16"?>
<xsd:schema
 xmlns:xsd="http://www.w3.org/2001/XMLSchema">
 <xsd:element name="parts">
  <xsd:complexType>
   <xsd:choice  minOccurs="0" maxOccurs="unbounded" > 
    <xsd:sequence>
     <xsd:element name="code" type="xsd:string"/>
     <xsd:element name="name" type="xsd:string"/>
     <xsd:element name="price" type="xsd:decimal"/>
    </xsd:sequence>
   </xsd:choice>
  </xsd:complexType>
 </xsd:element>
</xsd:schema>
'
CREATE TABLE Shipments
(
   ShipmentID INTEGER PRIMARY KEY NOT NULL,
   ShipmentDate DATETIME NOT NULL,
   ShippedParts XML (Parts)
)
GO

Open in new window


Please, give me a hint on what to change to make it work. More info can be found in attached file

Best regards
Marcus Steen
msg213.pdf
0
Comment
Question by:marcgu
  • 3
  • 2
5 Comments
 
LVL 32

Accepted Solution

by:
bhess1 earned 500 total points
ID: 37774048
You are missing the date field.  With your table having a shipping id, a shipment date, and the  shippedParts XML field, you need to include all three in your VALUES statement.  Try this:

INSERT INTO Shipments VALUES(1, CURRENT_TIMESTAMP, 
'<Root>
<Parts OrderID="1" PurchaseOrderNumber="NSSDJS#1">
<DeliveryDate>2006-12-30</DeliveryDate>
<Items>
   <ShipmentWeight>2.44</ShipmentWeight>
     <LineItem>
	        <Name>DentaKit for Adults</Name>
	        <Code>DK-R001</Code>
	        <Price>29.95</Price>
     </LineItem>
     <LineItem>
	        <Name>Retainer Brite 1 Year</Name>
	        <Code>DK-RB1Y</Code>
	        <Price>35.00</Price>
     </LineItem>
</Items>
</Parts>
</Root>')
GO

Open in new window

0
 

Author Comment

by:marcgu
ID: 37775145
Hi!

Thanks for your comment, It's leading me  closer to the solution, but still something is wrong because I now get the following error-message:

Msg 6913, Level 16, State 1, Line 1
XML Validation: Declaration not found for element 'Root'. Location: /*:Root[1]



I don't understand what I have to to do.
0
 
LVL 32

Expert Comment

by:bhess1
ID: 37789465
Your Schema doesn't have the <Root> element defined in it.  In a CREATE XML SCHEMA COLLECTION declaration, the first element defined becomes the root element of the document.  So, in your case, the root element is <Parts>.

There is a much more detailed explanation in this article at Beyond Relational, coder's website that I really like.
0
 

Author Comment

by:marcgu
ID: 37793118
Hi!

Thanks for the link.

 All of the examples in the article as well as this works now:

DECLARE @cust AS XML(Parts)
SET @cust = '<parts></parts>'

Open in new window


But, no matter how I try to change the code and remove <root></root> I, will still get msg6913 error message.

Msg 6913, Level 16, State 1, Line 1
XML Validation: Declaration not found for element 'Parts'. Location: /*:Parts[1]


For instance, I tried like this (and several other options)

INSERT INTO Shipments VALUES(1, CURRENT_TIMESTAMP, 
'<Parts OrderID="1" PurchaseOrderNumber="NSSDJS#1">
<DeliveryDate>2006-12-30</DeliveryDate>
<Items>
   <ShipmentWeight>2.44</ShipmentWeight>
     <LineItem>
	        <Name>DentaKit for Adults</Name>
	        <Code>DK-R001</Code>
	        <Price>29.95</Price>
     </LineItem>
     <LineItem>
	        <Name>Retainer Brite 1 Year</Name>
	        <Code>DK-RB1Y</Code>
	        <Price>35.00</Price>
     </LineItem>
</Items>
</Parts>')
GO

Open in new window




In case you or any one else has a final clue what´s going wrong for me, I will award you the points. You have actually answerd my original question. For me, it´s just annoying when I do not understand why I fail to follow the example.

/Marcus
0
 

Author Closing Comment

by:marcgu
ID: 37821543
This solved my initial question perfectly.

However, the solution showed that I had more problems which I probably have to adress in a new question.
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

28 Experts available now in Live!

Get 1:1 Help Now