Hi all

I need to set up an xml import procedure that allows large-scale xml records
to be imported into multiple tables.  I am currently using bulkload to pull
in large files (200+meg each) which are basically flat in nature, only
importing into a single table.

Moving forward, I will be supplementing this with another import of records
that may have 1 or many sublevel nodes, and I am not sure how to go about
setting up an xsd to do this. 

For example, the following is simplified version of an xml record.  Once I
can get this working, I'll add and adjust as necessary to pull the entire
record in and assign to the appropriate tables.  For the example, the node
names are the same as the staging table column names that correspond.
Nodes directly under the "customer" node go into a table called "customer". 
Each node under equipment item should be inserted into the equipment staging
table utilizing the refid to keep it tied to the customer parent record.  The
same for each contact, inserted into a contact staging table with the
associated refid.

<systemRecord>
        <customer>
            <refID>abcde12345</refID>
            <customerName>Robert Smith</customer>
            <status>001-456-111</status>
              <equipment>
                    <equipmentItem>
                          <productNumber>b12245</productNumber>
                          <quantity>1</quantity>
                          <manufacturer>Acme</manufacturer>
                          <model>223a</model>
                    </equipmentItem>
                    <equipmentItem>
                          <productNumber>b13131</productNumber>
                          <quantity>1</quantity>
                          <manufacturer>Acme</manufacturer>
                          <model>229a</model>
                    </equipmentItem>
              </equipment>
              <contacts>
                    <contact>
                          <type>Primary</type>
                          <address>100 smith street</address>
                          <city>Smithville</city>
                          <state>MS</state>
                          <zip>12345</zip>
                          <accountNumber>ab12cd45</accountNumber> 
                    </contact>
                    <contact>
                          <type>Secondary</type>
                          <address>120 smith street</address>
                          <city>Smithville</city>
                          <state>MS</state>
                          <zip>12346</zip>                         
                          <accountNumber>ab12cd45</accountNumber> 
                    </contact>
              </contacts>
        </customer>
</systemRecord>


This is where I am with the xsd, tho I am reasonably sure it isn't correct :)


<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
  <xsd:element name="systemRecord" nillable="true" type="systemRecord" />
  <xsd:complexType name="systemRecord">
    <xsd:sequence>
      <xsd:element minOccurs="0" maxOccurs="unbounded" name="customer"
type="customer" />
    </xsd:sequence>
  </xsd:complexType>
  <xsd:complexType name="customer">
    <xsd:sequence>
      <xsd:element minOccurs="0" maxOccurs="1" name="equipment"
type="ArrayOfEquipment" />
      <xsd:element minOccurs="0" maxOccurs="1" name="contacts"
type="ArrayOfContact" />
      <xsd:element minOccurs="0" maxOccurs="1" name="refID"
type="xsd:string" />
      <xsd:element minOccurs="0" maxOccurs="1" name="customerName"
type="xsd:string" />
      <xsd:element minOccurs="0" maxOccurs="1" name="status"
type="xsd:string" />
    </xsd:sequence>
  </xsd:complexType>
  <xsd:complexType name="ArrayOfEquipment">
    <xsd:sequence>
      <xsd:element minOccurs="0" maxOccurs="unbounded" name="equipmentItem"
nillable="true" type="equipmentItem" />
    </xsd:sequence>
  </xsd:complexType>
  <xsd:complexType name="equipmentItem">
    <xsd:sequence>
      <xsd:element minOccurs="0" maxOccurs="1" name="productNumber"
type="xsd:string" />
      <xsd:element minOccurs="0" maxOccurs="1" name="quantity"
type="xsd:string" />
      <xsd:element minOccurs="0" maxOccurs="1" name="manufacturer"
type="xsd:string" />
      <xsd:element minOccurs="0" maxOccurs="1" name="model"
type="xsd:string" />
    </xsd:sequence>
  </xsd:complexType>
  <xsd:complexType name="ArrayOfContact">
    <xsd:sequence>
      <xsd:element minOccurs="0" maxOccurs="unbounded" name="contact"
nillable="true" type="contact" />
    </xsd:sequence>
  </xsd:complexType>
  <xsd:complexType name="contact">
    <xsd:sequence>
      <xsd:element minOccurs="0" maxOccurs="1" name="type" type="xsd:string"
/>
      <xsd:element minOccurs="0" maxOccurs="1" name="address" type="address"
/>
      <xsd:element minOccurs="0" maxOccurs="1" name="city" type="xsd:string"
/>
      <xsd:element minOccurs="0" maxOccurs="1" name="state"
type="xsd:string" />
      <xsd:element minOccurs="0" maxOccurs="1" name="zip" type="xsd:string" />
      <xsd:element minOccurs="0" maxOccurs="1" name="acountNumber"
type="xsd:string" />
    </xsd:sequence>
  </xsd:complexType>
</xsd:schema>


So, any help would be greatly appreciated!  Thanks much in advance!

Your XSD file is just going to validate your XML files on import, unless
you're planning to create annotated XML schemas for SQLXML bulk loading.
XQuery might work for what you described:

DECLARE @x xml;
SET @x = N'<systemRecord>
<customer>
<refID>abcde12345</refID>
<customerName>Robert Smith</customerName>
<status>001-456-111</status>
<equipment>
<equipmentItem>
<productNumber>b12245</productNumber>
<quantity>1</quantity>
<manufacturer>Acme</manufacturer>
<model>223a</model>
</equipmentItem>
<equipmentItem>
<productNumber>b13131</productNumber>
<quantity>1</quantity>
<manufacturer>Acme</manufacturer>
<model>229a</model>
</equipmentItem>
</equipment>
<contacts>
<contact>
<type>Primary</type>
<address>100 smith street</address>
<city>Smithville</city>
<state>MS</state>
<zip>12345</zip>
<accountNumber>ab12cd45</accountNumber>
</contact>
<contact>
<type>Secondary</type>
<address>120 smith street</address>
<city>Smithville</city>
<state>MS</state>
<zip>12346</zip>
<accountNumber>ab12cd45</accountNumber>
</contact>
</contacts>
</customer>
</systemRecord>';

SELECT c.value(N'(customer/refID)[1]', N'nvarchar(100)') AS refID,
c.value(N'(customer/customerName)[1]', N'nvarchar(100)') AS customerName,
c.value(N'(customer/status)[1]', N'nvarchar(100)') AS status
FROM @x.nodes(N'//systemRecord') t(c);

SELECT c.value(N'(customer/refID)[1]', N'nvarchar(100)') AS refID,
d.value(N'(productNumber)[1]', N'nvarchar(100)') AS productNumber,
d.value(N'(quantity)[1]', N'int') AS quantity,
d.value(N'(manufacturer)[1]', N'nvarchar(100)') AS manufacturer,
d.value(N'(model)[1]', N'nvarchar(100)') AS model
FROM @x.nodes(N'//systemRecord') t(c)
CROSS APPLY c.nodes(N'//equipment/equipmentItem') u(d);

SELECT c.value(N'(customer/refID)[1]', N'nvarchar(100)') AS refID,
d.value(N'(type)[1]', N'nvarchar(100)') AS type,
d.value(N'(address)[1]', N'nvarchar(100)') AS address,
d.value(N'(city)[1]', N'nvarchar(100)') AS city,
d.value(N'(state)[1]', N'nvarchar(100)') AS state,
d.value(N'(zip)[1]', N'nvarchar(100)') AS zip,
d.value(N'(accountNumber)[1]', N'nvarchar(100)') AS accountNumber
FROM @x.nodes(N'//systemRecord') t(c)
CROSS APPLY c.nodes(N'//contacts/contact') u(d);

Show quoteHide quote

XML EXPLICIT Help
OPENXML Question
Parsing an XML string
xml datatype and SELECT ... FROM @xml
Shred XML question
OPENXML to accept both attributes and elements
XQuery - Only return if not null
XML parsing: ... illegal xml character
Exporting SQL Server data to XML
Retrieving XML Field data as Text