importing xml

• troy singleton

  SSC Journeyman

  Points: 87

  More actions

  February 2, 2006 at 5:26 pm

  #170874

  we are to receive an xml file hourly from a vendor. based on data in that file, i need to update the database. at this point, i have only received a .xsd file. with that, i generated a sample xml file using XMLSpy. When i try to use sqlxmlbulkload to get the data from the file into the database, i get an error, "schema: relationship expected on ''subscriber''. I have tried the load with the flags to generate the table(s) and same error. openxml is not really an option, from my understanding. this needs to be automated as a Job. I'd really rather not use msxml.domdocument to to it's verbosity. below is the .xsd and .xml. if anyone has any pointers, i would be forever grateful. btw, i have also tried making a "mapping" file to use in place of the .xsd, which works, until i get to the point where i have to deal with nested nodes (parent --> child --> grandchildren???)

  ------------xsd--------------

  <?xml version="1.0" encoding="UTF-8"?>

  <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified" attributeFormDefault="unqualified">

   <xs:element name="sList">

    <xs:complexType>

     <xs:sequence>

      <xs:element name="sub" maxOccurs="unbounded">

       <xs:complexType>

        <xs:sequence>

         <xs:element name="subNum"/>

         <xs:element name="refNum"/>

         <xs:element name="ordStat"/>

         <xs:element name="serv" maxOccurs="unbounded">

          <xs:complexType>

           <xs:sequence>

            <xs:element name="pNum"/>

            <xs:element name="aNum" minOccurs="0" maxOccurs="2"/>

            <xs:element name="sStat"/>

            <xs:element name="actDt" minOccurs="0"/>

            <xs:element name="clDt" minOccurs="0"/>

            <xs:element name="eStat"/>

            <xs:element name="lDev" maxOccurs="2">

             <xs:complexType>

              <xs:sequence>

               <xs:element name="devNum"/>

               <xs:element name="devPt"/>

               <xs:element name="sUId"/>

               <xs:element name="sAUser"/>

               <xs:element name="sAP"/>

               <xs:element name="iP"/>

               <xs:element name="oP"/>

              </xs:sequence>

             </xs:complexType>

            </xs:element>

           </xs:sequence>

          </xs:complexType>

         </xs:element>

         <xs:element name="dev" maxOccurs="unbounded">

          <xs:complexType>

           <xs:sequence>

            <xs:element name="devNum"/>

            <xs:element name="devTp"/>

            <xs:element name="mA" minOccurs="0"/>

            <xs:element name="sNum" minOccurs="0"/>

            <xs:element name="sDt" minOccurs="0"/>

            <xs:element name="tkNum" minOccurs="0"/>

           </xs:sequence>

          </xs:complexType>

         </xs:element>

        </xs:sequence>

       </xs:complexType>

      </xs:element>

     </xs:sequence>

    </xs:complexType>

   </xs:element>

  </xs:schema>

  --------------xml--------------------

  <sList>

   <sub>

    <subNumr>0001</subscriberNumber>

    <refNum>555666</referenceNumber>

    <ordStat/>

    <serv>

     <pNum>8055556666</pNum>

     <aNum>8055554444</aNum>

     <sStat>Active</sStat>

     <actDt>01/01/2006</actDt>

     <clDt/>

     <eStat>Pass</eStat>

     <lDev>

      <devNum>1</devNum>

      <devPt>1</devPt>

      <sUId>000000001</sUId>

      <sAU>user</sAU>

      <sAP>password</sAP>

      <iP>111.111.1.11</iP>

      <oP>222.222.2.22</oP>

     </lDev>

    </serv>

    <dev>

     <devNum>1</devNum>

     <devTp>ATA</devTp>

     <mA>011fvgt343</mA>

     <sNum>3434343434343</sNum>

     <sDt>01/01/2006</sDt>

     <tNum>58585858</tNum>

    </dev>

   </sub>

  </sList>