SQLXMLBulk Load

[EDN Admin]

Im trying to insert the following XML file into SQL Server 2008 using SQLXMLBULKLOAD. The XML file is quite big so Ive only posted a portion of the file. However, Im having some problems with setting up the schema. When I try to run the program, it gives
me an error stating: "Schema: relationship expected on datacol". I tried to add the following relationship but i still receive an error. If someone could help out that would be great thanks!

<pre class="x_default x_prettyprint

<span class="pln   <span class="tag <xs:annotation><span class="pln <br/>    <span class="tag <xs:appinfo><span class="pln <br/>      <span class="tag <sql:relationship<span class="pln  <span class="atn name<span class="pun =<span class="atv "TableRel"<span class="pln    <br/>                        <span class="atn parent<span class="pun =<span class="atv "UserCustomer"<span class="pln <br/>          <span class="atn parent-key<span class="pun =<span class="atv "id"<span class="pln <br/><span class="atn child<span class="pun =<span class="atv "UserCustomer"<span class="pln <br/><span class="atn child-key<span class="pun =<span class="atv "name2"<span class="tag /><span class="pln <br/>    <span class="tag </xs:appinfo><span class="pln <br/>  <span class="tag </xs:annotation><span class="pln <br/>

[/code]
I have the table setup as this in SQL:
<pre class="x_default x_prettyprint

<span class="typ Create<span class="pln  table <span class="typ UserCustomer<span class="pun (<span class="pln <br/>name varchar<span class="pun (<span class="pln MAX<span class="pun ),<span class="pln <br/>id <span class="kwd int<span class="pln  <span class="typ Primary<span class="pln  KEY<span class="pun ,<span class="pln <br/>datacol varchar<span class="pun (<span class="pln MAX<span class="pun ),<span class="pln <br/>name2 varchar<span class="pun (<span class="pln MAX<span class="pun )<span class="pln <br/><span class="pun )<span class="pln <br/>

[/code]

XML:
<pre class="x_default x_prettyprint

<span class="tag <all><span class="pln <br/>  <span class="tag <table><span class="pln <br/>    <span class="tag <record<span class="pln  <span class="atn id<span class="pun =<span class="atv "1001"<span class="tag ><span class="pln <br/>      <span class="tag <datacol<span class="pln  <span class="atn name<span class="pun =<span class="atv "Status"<span class="tag ><span class="pln  <span class="tag </datacol><span class="pln <br/>      <span class="tag <datacol<span class="pln  <span class="atn name<span class="pun =<span class="atv "pkey"<span class="tag ><span class="pln 0000000000e2zww2<span class="tag </datacol><span class="pln <br/>      <span class="tag <datacol<span class="pln  <span class="atn name<span class="pun =<span class="atv "active"<span class="tag ><span class="pln 1<span class="tag </datacol><span class="pln <br/>      <span class="tag <datacol<span class="pln  <span class="atn name<span class="pun =<span class="atv "bpamainhkey"<span class="tag ><span class="pln 0000320583<span class="tag </datacol><span class="pln <br/>      <span class="tag <datacol<span class="pln  <span class="atn name<span class="pun =<span class="atv "firstname"<span class="tag ><span class="pln Steve<span class="tag </datacol><span class="pln <br/>      <span class="tag <datacol<span class="pln  <span class="atn name<span class="pun =<span class="atv "lastname"<span class="tag ><span class="pln Zmudczynski<span class="tag </datacol><span class="pln <br/>      <span class="tag <datacol<span class="pln  <span class="atn name<span class="pun =<span class="atv "loginname"<span class="tag ><span class="pln CORPSXZMUDCZ<span class="tag </datacol><span class="pln <br/>      <span class="tag <datacol<span class="pln  <span class="atn name<span class="pun =<span class="atv "lppd"<span class="tag ><span class="pln 20120521<span class="tag </datacol><span class="pln <br/>      <span class="tag <datacol<span class="pln  <span class="atn name<span class="pun =<span class="atv "main"<span class="tag ><span class="pln 1<span class="tag </datacol><span class="pln <br/>      <span class="tag <datacol<span class="pln  <span class="atn name<span class="pun =<span class="atv "managementtype"<span class="tag ><span class="pln Sales<span class="tag </datacol><span class="pln <br/>      <span class="tag <datacol<span class="pln  <span class="atn name<span class="pun =<span class="atv "validfrom"<span class="tag ><span class="pln 19000101<span class="tag </datacol><span class="pln <br/>      <span class="tag <datacol<span class="pln  <span class="atn name<span class="pun =<span class="atv "validthru"<span class="tag ><span class="pln 20120221<span class="tag </datacol><span class="pln <br/>    <span class="tag </record<span class="pln  <span class="tag ><span class="pln <br/><span class="tag </table><span class="pln <br/><span class="tag </all><span class="pln <br/>

[/code]

Schema:

<pre class="x_default x_prettyprint

<span class="pun <?<span class="pln xml version<span class="pun =<span class="str "1.0"<span class="pln  encoding<span class="pun =<span class="str "iso-8859-1"<span class="pun ?><span class="pln <br/><span class="tag <xs:schema<span class="pln  <span class="atn attributeFormDefault<span class="pun =<span class="atv "unqualified"<span class="pln  <span class="atn elementFormDefault<span class="pun =<span class="atv "qualified"<span class="pln  <span class="atn xmlns:xs<span class="pun =<span class="atv "http://www.w3.org/2001/XMLSchema"<span class="tag ><span class="pln <br/>  <span class="tag <xs:element<span class="pln  <span class="atn name<span class="pun =<span class="atv "all"<span class="tag ><span class="pln <br/>    <span class="tag <xs:complexType><span class="pln <br/>      <span class="tag <xs:sequence><span class="pln <br/>        <span class="tag <xs:element<span class="pln  <span class="atn name<span class="pun =<span class="atv "table"<span class="tag ><span class="pln <br/>          <span class="tag <xs:complexType><span class="pln <br/>            <span class="tag <xs:sequence><span class="pln <br/>              <span class="tag <xs:element<span class="pln  <span class="atn maxOccurs<span class="pun =<span class="atv "unbounded"<span class="pln  <span class="atn name<span class="pun =<span class="atv "record"<span class="tag ><span class="pln <br/>                <span class="tag <xs:complexType><span class="pln <br/>                  <span class="tag <xs:sequence><span class="pln <br/>                    <span class="tag <xs:element<span class="pln  <span class="atn maxOccurs<span class="pun =<span class="atv "unbounded"<span class="pln  <span class="atn name<span class="pun =<span class="atv "datacol"<span class="pln  <span class="atn sql:relationship<span class="pun =<span class="atv "TableRel"<span class="tag ><span class="pln <br/>                      <span class="tag <xs:complexType><span class="pln <br/>                        <span class="tag <xs:simpleContent><span class="pln <br/>                          <span class="tag <xs:extension<span class="pln  <span class="atn base<span class="pun =<span class="atv "xs:string"<span class="tag ><span class="pln <br/>                            <span class="tag <xs:attribute<span class="pln  <span class="atn name<span class="pun =<span class="atv "name"<span class="pln  <span class="atn type<span class="pun =<span class="atv "xs:string"<span class="pln  <span class="atn use<span class="pun =<span class="atv "required"<span class="pln  <span class="tag /><span class="pln <br/>                          <span class="tag </xs:extension><span class="pln <br/>                        <span class="tag </xs:simpleContent><span class="pln <br/>                      <span class="tag </xs:complexType><span class="pln <br/>                    <span class="tag </xs:element><span class="pln <br/>                  <span class="tag </xs:sequence><span class="pln <br/>                  <span class="tag <xs:attribute<span class="pln  <span class="atn name<span class="pun =<span class="atv "id"<span class="pln  <span class="atn type<span class="pun =<span class="atv "xs:unsignedShort"<span class="pln  <span class="atn use<span class="pun =<span class="atv "required"<span class="pln  <span class="tag /><span class="pln <br/>                <span class="tag </xs:complexType><span class="pln <br/>              <span class="tag </xs:element><span class="pln <br/>            <span class="tag </xs:sequence><span class="pln <br/>          <span class="tag </xs:complexType><span class="pln <br/>        <span class="tag </xs:element><span class="pln <br/>      <span class="tag </xs:sequence><span class="pln <br/>    <span class="tag </xs:complexType><span class="pln <br/>  <span class="tag </xs:element><span class="pln <br/><span class="tag </xs:schema><span class="pln <br/>

[/code]



View the full article