How to import XML

I grabbed the following .xml and .xsd files from an online tutorial, and tried to import them into SQL Server by using XML Source. I want to be able to import ALL the data; however, the simpleType elements under the root <shiporder> are all missing from the XML source, e.g <orderpersion>. The root element, including any attributes from the root element are also missing...e.g <shiporder orderid="889923"

How can I import these missing fields? The .xml and .xsd files are below.

Any help will be greatly appreciated. Thanks.

.XML and .XSD files below. please note that the website replaces ": s" with

shiporder.xml
-
<?xml version="1.0" encoding="ISO-8859-1"?>

<shiporder orderid="889923"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="shiporder.xsd">
<orderperson>John Smith</orderperson>
<shipto>
<name>Ola Nordmann</name>
<address>Langgt 23</address>
<city>4000 Stavanger</city>
<country>Norway</country>
</shipto>
<item>
<title>Empire Burlesque</title>
<note>Special Edition</note>
<quantity>1</quantity>
<price>10.90</price>
</item>
<item>
<title>Hide your heart</title>
<quantity>1</quantity>
<price>9.90</price>
</item>
</shiporder>
-

shiporder.xsd...
-
<?xml version="1.0" encoding="ISO-8859-1" ?>
<xschema xmlns:xs="http://www.w3.org/2001/XMLSchema">

<xsimpleType name="stringtype">
<xs:restriction base="xstring"/>
</xsimpleType>

<xsimpleType name="inttype">
<xs:restriction base="xsositiveInteger"/>
</xsimpleType>

<xsimpleType name="dectype">
<xs:restriction base="xsecimal"/>
</xsimpleType>

<xsimpleType name="orderidtype">
<xs:restriction base="xstring">
<xsattern value="[0-9]{6}"/>
</xs:restriction>
</xsimpleType>

<xs:complexType name="shiptotype">
<xsequence>
<xs:element name="name" type="stringtype"/>
<xs:element name="address" type="stringtype"/>
<xs:element name="city" type="stringtype"/>
<xs:element name="country" type="stringtype"/>
</xsequence>
</xs:complexType>

<xs:complexType name="itemtype">
<xsequence>
<xs:element name="title" type="stringtype"/>
<xs:element name="note" type="stringtype" minOccurs="0"/>
<xs:element name="quantity" type="inttype"/>
<xs:element name="price" type="dectype"/>
</xsequence>
</xs:complexType>

<xs:complexType name="shipordertype">
<xsequence>
<xs:element name="orderperson" type="stringtype"/>
<xs:element name="shipto" type="shiptotype"/>
<xs:element name="item" maxOccurs="unbounded" type="itemtype"/>
</xsequence>
<xs:attribute name="orderid" type="orderidtype" use="required"/>
</xs:complexType>

<xs:element name="shiporder" type="shipordertype"/>

</xschema>

The XML Source expects a dummy root for some reason. Adding one to the XML and XSD should get you what you need.

Code Snippet

<root>
<shiporder orderid="889923"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="shiporder.xsd">
<orderperson>John Smith</orderperson>
<shipto>
<name>Ola Nordmann</name>
<address>Langgt 23</address>
<city>4000 Stavanger</city>
<country>Norway</country>
</shipto>
<item>
<title>Empire Burlesque</title>
<note>Special Edition</note>
<quantity>1</quantity>
<price>10.90</price>
</item>
<item>
<title>Hide your heart</title>
<quantity>1</quantity>
<price>9.90</price>
</item>
</shiporder>
</root>

Code Snippet

<xs:complexType name="roottype">
<xs:sequence>
<xs:element name="shiporder" type="shipordertype"/>
</xs:sequence>
</xs:complexType>

<xs:element name="root" type="roottype"/>

|||

Hey JayH,

That was it. Thanks a lot.

how to impliment "array" in stored procedure?

i doing an online shop project which have an shoppingcart and it stored database.
and i have the situation like this.
the products have properties such as size, color . and customerscan buy a product with particular size or color. and i havethe shopping cart table structure and data like following
Id(primary key) CartId productId size color quantity
1 1 1 S red 10
2 1 1 S black 2
3 1 1 S blue 3
4 1 1 M red 5
5 1 1 L blue 2
all the data above is i image the customer may inputed. And myproblem is how to use an stored procedure to updata above record when a customer buy the same product which is one of the product fromabove(have same productId, size, color)
and i try to use the following code but it didn't work
<code>
create procedure shoppingcart_add_item
( @.cartId int,
@.productId int,
@.size nvarchar(20),
@.color nvarchar(20),
@.quantity int
)
AS
DECLARE @.countproduct
DECLARE @.oldsize
DECLARE @.oldcolor
select @.countproduct=count(productId) FROM shoppingcart WHERE productId=@.productId AND cartId=@.cartId
select @.oldsize=size,@.oldcolor=color FROM shoppingcart WHERE productId=@.productId
IF @.CountItems > 0 and @.oldsize = @.size and @.oldcolor = @.color
UPDATE
ShoppingCart
SET
Quantity = (@.Quantity + ShoppingCart.Quantity)
WHERE
ProductId = @.ProductId
AND
CartId = @.CartId
ELSE /* New entry for this Cart. Add a new record */
INSERT INTO ShoppingCart
(
CartId,
ProductId,
Quantity,
color,
size
)
VALUES
(
@.CartId,
@.ProductId,
@.Quantity,
@.size,
@.color
)
</CODE>
and the result from this stored procedure is not what i want, what itry to say is can i stored all the size and color in @.oldsize and@.oldcolor array. then loop through the array to get the one iwant??
somebody get any idea? or don't know what i am talking about?
sorry i asked a very stupid quesation, i just think too much, actually the solution is increditable simple, it just need alittle bit SQL can slove this(simple get the Id and update the newrecord depend on this Id)
i don't know why i am so stupid to make things complicated.