XML Bulk Import Schema - recursive

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

XML Bulk Import Schema - recursive

Author

Topic

Kristen
Test

22859 Posts

Posted - 2006-06-24 : 07:59:55

I have some XML which recursively describes Attributes for products (Colour/Size/Leg length/etc.)

In reality I can impose a limit of 5 levels

I am using SQL XML Bulk Load (SQLXMLBulkLoad V3.0) to grab the XML into staging tables, but I'm rubbish at XML / Schemas, and I don't know how to define it to get the attributes.

I have treated the SCS_ITEM stuff as "just being there", regardless of the recursion, and I'm using a sql:relation from the /STOCK_ITEM/SCS/STYLE/.../STYLE/SCS_ITEM/STOCK_CODE
to get its associated "parent" /STOCK_ITEM/STOCK_CODE

If anyone can help with the schema I'd appreciate it.


<STOCK_ITEM>
	<STOCK_CODE>05BER33082</STOCK_CODE>
	<SCS>
		<STYLE>
			<DESCRIPTION>black</DESCRIPTION>
			<STYLE>
				<DESCRIPTION>small</DESCRIPTION>
				<SCS_ITEM>
					<STOCK_CODE>SKCODE-BK-S-</STOCK_CODE>
					<DESCRIPTION>DESC black / small /</DESCRIPTION>
				</SCS_ITEM>
			</STYLE>
			<STYLE>
				<DESCRIPTION>med</DESCRIPTION>
				<SCS_ITEM>
					<STOCK_CODE>SKCODE-BK-M-</STOCK_CODE>
					<DESCRIPTION>DESC black / med /</DESCRIPTION>
				</SCS_ITEM>
			</STYLE>
		</STYLE>
		<STYLE>
			<DESCRIPTION>pink</DESCRIPTION>
			<STYLE>
				<DESCRIPTION>small</DESCRIPTION>
				<SCS_ITEM>
					<STOCK_CODE>SKCODE-PI-S-</STOCK_CODE>
					<DESCRIPTION>DESC pink / small /</DESCRIPTION>
				</SCS_ITEM>
			</STYLE>
			<STYLE>
				<DESCRIPTION>med</DESCRIPTION>
				<SCS_ITEM>
					<STOCK_CODE>SKCODE-PI-M-</STOCK_CODE>
					<DESCRIPTION>DESC pink / med /</DESCRIPTION>
				</SCS_ITEM>
			</STYLE>
		</STYLE>
		<HEADING LEVEL="1">Colour</HEADING>
		<HEADING LEVEL="2">Size</HEADING>
	</SCS>
</STOCK_ITEM>
<STOCK_ITEM>
	<STOCK_CODE>05BERCOL2</STOCK_CODE>
	<SCS>
		<STYLE>
			<DESCRIPTION>LeftHand</DESCRIPTION>
			<SCS_ITEM>
				<STOCK_CODE>05BERCOL2-LHZ--</STOCK_CODE>
				<DESCRIPTION>Col Sleeping Bag LeftHand /  /</DESCRIPTION>
			</SCS_ITEM>
		</STYLE>
		<STYLE>
			<DESCRIPTION>RightHand</DESCRIPTION>
			<SCS_ITEM>
				<STOCK_CODE>05BERCOL2-RHZ--</STOCK_CODE>
				<DESCRIPTION>Col Sleeping Bag RightHand /  /</DESCRIPTION>
			</SCS_ITEM>
		</STYLE>
		<HEADING LEVEL="1">Zip</HEADING>
	</SCS>
</STOCK_ITEM>

what I'm looking for is something like:


STOCK_CODE      Attribute1 Attribute2 Attribute3
=============== ========== ========== ==========
SKCODE-BK-S-    black      small      NULL
SKCODE-BK-M-    black      med        NULL
SKCODE-PI-S-    pink       small      NULL
SKCODE-PI-M-    pink       med        NULL
05BERCOL2-LHZ-- LeftHand   NULL       NULL
05BERCOL2-RHZ-- RightHand  NULL       NULL

Getting the attributes into a separate table would be fine too:


STOCK_CODE      Attribute Value
=============== ========= ==========
SKCODE-BK-S-    1         black      
SKCODE-BK-S-    2         small
SKCODE-BK-M-    1         black
SKCODE-BK-M-    1         med
...
05BERCOL2-LHZ-- 1         LeftHand
05BERCOL2-RHZ-- 1         RightHand

and I also need the attribute class names


MODULE_CODE     Level      Name
=============== ========= ==========
05BER33082      1         Colour
05BER33082      2         Size
05BERCOL2       1         Zip

and here is the Schema, so far


<?xml version="1.0" encoding="utf-16"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" 
		xmlns:xs="http://www.w3.org/2001/XMLSchema"
		xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
  <xs:annotation>
    <xs:appinfo>
      <sql:relationship name="STOCK_SCS" parent="staging_STOCK_ITEM" parent-key="STOCK_CODE" 
					child="staging_SCS_ITEM" child-key="ASSOCIATED_MODULE"/>
      <sql:relationship name="STOCK_HEADING" parent="staging_STOCK_ITEM" parent-key="STOCK_CODE" 
					child="staging_STOCK_HEADING" child-key="STOCK_CODE"/>
    </xs:appinfo>
  </xs:annotation>
  <xs:element name="STOCK_ITEMS" sql:is-constant="1">
    <xs:complexType>
      <xs:sequence>
        <xs:element maxOccurs="unbounded" name="STOCK_ITEM" sql:relation="staging_STOCK_ITEM">
          <xs:complexType>
            <xs:sequence>
              <xs:element name="STOCK_CODE" type="xs:unsignedShort"/>
              <xs:element minOccurs="0" name="SCS" sql:is-constant="1">
                <xs:complexType>
                  <xs:sequence>
                    <xs:element maxOccurs="unbounded" name="STYLE" sql:is-constant="1">
                      <xs:complexType>
                        <xs:sequence>
                          <xs:element name="DESCRIPTION" type="xs:string" sql:mapped="false"/>

                          <xs:element minOccurs="0" name="SCS_ITEM" sql:relation="staging_SCS_ITEM" 
						sql:relationship="STOCK_SCS">
                            <xs:complexType>
                              <xs:sequence>
                                <xs:element name="STOCK_CODE" type="xs:string"/>
                                <xs:element name="DESCRIPTION" type="xs:string"/>
                              </xs:sequence>
                            </xs:complexType>
                          </xs:element>

                          <xs:element minOccurs="0" maxOccurs="unbounded" name="STYLE" sql:is-constant="1">
                            <xs:complexType>
                              <xs:sequence>
                                <xs:element name="DESCRIPTION" type="xs:string" sql:mapped="false"/>

                                <xs:element name="SCS_ITEM" sql:relation="staging_SCS_ITEM" 
						sql:relationship="STOCK_SCS">
                                  <xs:complexType>
                                    <xs:sequence>
                                      <xs:element name="STOCK_CODE" type="xs:string"/>
                                      <xs:element name="DESCRIPTION" type="xs:string"/>
                                    </xs:sequence>
                                  </xs:complexType>
                                </xs:element>

                              </xs:sequence>
                            </xs:complexType>
                          </xs:element>

                        </xs:sequence>
                      </xs:complexType>
                    </xs:element>

                    <xs:element minOccurs="0" maxOccurs="unbounded" name="HEADING" 
					sql:relation="staging_STOCK_HEADING" 
					sql:relationship="STOCK_HEADING">
                      <xs:complexType>
                        <xs:simpleContent>
                          <xs:extension base="xs:string">
                            <xs:attribute name="LEVEL" type="xs:unsignedShort" use="required"/>
                          </xs:extension>
                        </xs:simpleContent>
                      </xs:complexType>
                    </xs:element>

                  </xs:sequence>
                </xs:complexType>
              </xs:element>
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources