How to import XML

Question

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>

Answer 1

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"/>

Answer 2

Hey JayH,

 

That was it.  Thanks a lot.