none
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 Tongue Tied

    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" ?>
    <xsTongue Tiedchema xmlns:xs="http://www.w3.org/2001/XMLSchema">

    <xsTongue TiedimpleType name="stringtype">
     <xs:restriction base="xsTongue Tiedtring"/>
    </xsTongue TiedimpleType>

    <xsTongue TiedimpleType name="inttype">
     <xs:restriction base="xsStick out tongueositiveInteger"/>
    </xsTongue TiedimpleType>

    <xsTongue TiedimpleType name="dectype">
     <xs:restriction base="xsBig Smileecimal"/>
    </xsTongue TiedimpleType>

    <xsTongue TiedimpleType name="orderidtype">
     <xs:restriction base="xsTongue Tiedtring">
      <xsStick out tongueattern value="[0-9]{6}"/>
     </xs:restriction>
    </xsTongue TiedimpleType>

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

    <xs:complexType name="itemtype">
     <xsTongue Tiedequence>
      <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"/>
     </xsTongue Tiedequence>
    </xs:complexType>

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

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

    </xsTongue Tiedchema>

    Wednesday, April 25, 2007 12:40 PM

Answers

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


    Wednesday, April 25, 2007 1:05 PM

All replies

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


    Wednesday, April 25, 2007 1:05 PM
  • Hey JayH,

     

    That was it.  Thanks a lot.

    Wednesday, April 25, 2007 3:20 PM