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 Load XDS Schema problem.

Author  Topic 

dgaylor
Yak Posting Veteran

54 Posts
Posted - 2004-04-21 : 13:21:42
I have XML Data that I am trying to load into sql server 2000 using bulk load. I am having problems trying to get a XDS that works. 5 Tables need to be populated, and all of them need to include the value for personid that is only mentioned once per person. The EDUCATION section and the email_id can have multiple occurances. Any help is really appreciated. Attached is sample data, table, and the xsd that I cannot get to work. Thanks.


SAMPLE DATA:
<?xml version="1.0" encoding="windows-1252"?> <ROOT>
<LIST>
<personid>1</personid><last_upd>08/27/03</last_upd><NAME><name_prefix/><first_name>Darren</first_name><middle_name>D</middle_name><last_name>Smith</last_name><name_suffix/></NAME><occ_desc>Programmer</occ_desc><MAILING_ADDRESS/><BIRTH_DATE><birth_month>05</birth_month><birth_day>22</birth_day><birth_year>1947</birth_year><birth_city>N.Y.C.</birth_city><birth_state/><birth_nation/></BIRTH_DATE><gender>M</gender><politics> Republican</politics><religion/><EDUCATION><Degree>BA</Degree><School>L.I. U.</School><DegreeYear>1969</DegreeYear></EDUCATION><EDUCATION><Degree>MA</Degree><School>L.I. U.</School><DegreeYear>1971</DegreeYear></EDUCATION><EDUCATION><Degree>Profl. diploma</Degree><School>Hofstra U.</School><DegreeYear>1975</DegreeYear></EDUCATION><EDUCATION><Degree>EdD</Degree><School>Nova Southeastern U.</School><DegreeYear>1982</DegreeYear></EDUCATION><EDUCATION><Degree>JD</Degree><School>Touro Coll.</School><DegreeYear>1991</DegreeYear></EDUCATION></PAR><AVO/><CAR> Miller</CAR>
</LIST>
<LIST>
<personid>542</personid><last_upd>04/15/04</last_upd><NAME><name_prefix/><first_name>Colman</first_name><middle_name/><last_name>Jenkins</last_name><name_suffix/></NAME><occ_desc> Banker</occ_desc><MAILING_ADDRESS/><BIRTH_DATE><birth_month>09</birth_month><birth_day>24</birth_day><birth_year>1932</birth_year><birth_city>N.Y.C.</birth_city><birth_state/><birth_nation/></BIRTH_DATE><gender>M</gender><politics> Democrat</politics><religion> Jewish</religion><EDUCATION><Degree>BS</Degree><School>Bucknell U.</School><DegreeYear>1953</DegreeYear></EDUCATION><EDUCATION><Degree>MBA</Degree><School>NYU</School><DegreeYear>1962</DegreeYear></EDUCATION></PAR><AVO/><CAR> Microsoft</CAR>
</LIST>
<LIST>
<personid>666</personid><last_upd>04/03/04</last_upd><NAME><name_prefix/><first_name>John</first_name><middle_name>William</middle_name><last_name>Aber</last_name><name_suffix/></NAME><occ_desc> finance educator</occ_desc><MAILING_ADDRESS><street1/><street2/><street3>51 Main St</street3><city>Brookline</city><pr_urb/><state_province>MA</state_province><zip>02446-5588</zip><country/></MAILING_ADDRESS><BIRTH_DATE><birth_month>09</birth_month><birth_day>09</birth_day><birth_year>1963</birth_year><birth_city>Canonsburg</birth_city><birth_state>Pa.</birth_state><birth_nation/></BIRTH_DATE><gender>M</gender><politics/><religion/><EDUCATION><Degree>BS</Degree><School>Pa. State U.</School><DegreeYear>1988</DegreeYear></EDUCATION><EDUCATION><Degree>MBA</Degree><School>Columbia U.</School><DegreeYear>1965</DegreeYear></EDUCATION><EDUCATION><Degree>DBA</Degree><School>Harvard U.</School><DegreeYear>1990</DegreeYear></EDUCATION><PAR> John and Mary</PAR><AVO/><CAR> Apple, JohnJohn Inc</CAR>
</LIST></ROOT>

-----------------------------------
SQL TABLES:
CREATE TABLE [dbo].[BIRTH_DATE] (
[personid] [int] NULL ,
[birth_month] [char] (2) NULL ,
[birth_day] [char] (2) NULL ,
[birth_year] [char] (4) NULL ,
[birth_city] [char] (70) NULL ,
[birth_state] [char] (70) NULL ,
[birth_nation] [char] (70) NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[EDUCATION] (
[personid] [int] NULL ,
[degree] [char] (500) NULL ,
[school] [char] (500) NULL ,
[degreeyear] [char] (4) NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[LIST] (
[personid] [int] NULL ,
[last_upd] [char] (10) NULL ,
[occ_desc] [char] (200) NULL ,
[email_id] [char] (250) NULL ,
[gender] [char] (1) NULL ,
[politics] [char] (500) NULL ,
[religion] [char] (500) NULL ,
[car] [varchar] (8000) NULL ,
[par] [varchar] (8000) NULL ,
[avo] [varchar] (8000) NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[MAILING_ADDRESS] (
[personid] [int] NULL ,
[street1] [char] (60) NULL ,
[street2] [char] (40) NULL ,
[street3] [char] (40) NULL ,
[city] [char] (50) NULL ,
[pr_urb] [char] (40) NULL ,
[state_province] [char] (2) NULL ,
[zip] [char] (40) NULL ,
[country] [char] (40) NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[NAME] (
[personid] [int] NULL ,
[name_prefix] [char] (15) NULL ,
[first_name] [char] (70) NULL ,
[middle_name] [char] (70) NULL ,
[last_name] [char] (70) NULL ,
[name_suffix] [char] (15) NULL
) ON [PRIMARY]
GO

---------------------------------
XSLT SCHEMA:
<?xml version="1.0" encoding="utf-8"?>
<xslt:stylesheet version="1.0" xmlns:xslt="http://www.w3.org/1999/XSL/Transform">
<xslt:output method="xml" indent="yes" />
<xslt:template match="/">
<NewDataSet>
<xslt:apply-templates select="ROOT | */ROOT" />
</NewDataSet>
</xslt:template>
<xslt:template match="ROOT">
<ROOT>
<xslt:apply-templates select="LIST" />
</ROOT>
</xslt:template>
<xslt:template match="ROOT/LIST">
<LIST>
<xslt:apply-templates select="personid | last_upd | occ_desc | gender | politics | religion | PAR | AVO | CAR | NAME | MAILING_ADDRESS | BIRTH_DATE | EDUCATION" />
</LIST>
</xslt:template>
<xslt:template match="ROOT/LIST/personid">
<personid>
<xslt:apply-templates />
</personid>
</xslt:template>
<xslt:template match="ROOT/LIST/last_upd">
<last_upd>
<xslt:apply-templates />
</last_upd>
</xslt:template>
<xslt:template match="ROOT/LIST/occ_desc">
<occ_desc>
<xslt:apply-templates />
</occ_desc>
</xslt:template>
<xslt:template match="ROOT/LIST/gender">
<gender>
<xslt:apply-templates />
</gender>
</xslt:template>
<xslt:template match="ROOT/LIST/politics">
<politics>
<xslt:apply-templates />
</politics>
</xslt:template>
<xslt:template match="ROOT/LIST/religion">
<religion>
<xslt:apply-templates />
</religion>
</xslt:template>
<xslt:template match="ROOT/LIST/PAR">
<par>
<xslt:apply-templates />
</par>
</xslt:template>
<xslt:template match="ROOT/LIST/AVO">
<avo>
<xslt:apply-templates />
</avo>
</xslt:template>
<xslt:template match="ROOT/LIST/CAR">
<car>
<xslt:apply-templates />
</car>
</xslt:template>
<xslt:template match="ROOT/LIST/NAME">
<xslt:apply-templates select="name_prefix | first_name | middle_name | last_name | name_suffix" />
</xslt:template>
<xslt:template match="ROOT/LIST/MAILING_ADDRESS">
<xslt:apply-templates select="street1 | street2 | street3 | city | pr_urb | state_province | zip | country" />
</xslt:template>
<xslt:template match="ROOT/LIST/BIRTH_DATE">
<xslt:apply-templates select="birth_month | birth_day | birth_year | birth_city | birth_state | birth_nation" />
</xslt:template>
<xslt:template match="ROOT/LIST/EDUCATION">
<xslt:apply-templates select="Degree | School | DegreeYear" />
</xslt:template>
<xslt:template match="ROOT/LIST/NAME/name_prefix">
<name_prefix>
<xslt:apply-templates />
</name_prefix>
</xslt:template>
<xslt:template match="ROOT/LIST/NAME/first_name">
<first_name>
<xslt:apply-templates />
</first_name>
</xslt:template>
<xslt:template match="ROOT/LIST/NAME/middle_name">
<middle_name>
<xslt:apply-templates />
</middle_name>
</xslt:template>
<xslt:template match="ROOT/LIST/NAME/last_name">
<last_name>
<xslt:apply-templates />
</last_name>
</xslt:template>
<xslt:template match="ROOT/LIST/NAME/name_suffix">
<name_suffix>
<xslt:apply-templates />
</name_suffix>
</xslt:template>
<xslt:template match="ROOT/LIST/MAILING_ADDRESS/street1">
<street1>
<xslt:apply-templates />
</street1>
</xslt:template>
<xslt:template match="ROOT/LIST/MAILING_ADDRESS/street2">
<street2>
<xslt:apply-templates />
</street2>
</xslt:template>
<xslt:template match="ROOT/LIST/MAILING_ADDRESS/street3">
<street3>
<xslt:apply-templates />
</street3>
</xslt:template>
<xslt:template match="ROOT/LIST/MAILING_ADDRESS/city">
<city>
<xslt:apply-templates />
</city>
</xslt:template>
<xslt:template match="ROOT/LIST/MAILING_ADDRESS/pr_urb">
<pr_urb>
<xslt:apply-templates />
</pr_urb>
</xslt:template>
<xslt:template match="ROOT/LIST/MAILING_ADDRESS/state_province">
<state_province>
<xslt:apply-templates />
</state_province>
</xslt:template>
<xslt:template match="ROOT/LIST/MAILING_ADDRESS/zip">
<zip>
<xslt:apply-templates />
</zip>
</xslt:template>
<xslt:template match="ROOT/LIST/MAILING_ADDRESS/country">
<country>
<xslt:apply-templates />
</country>
</xslt:template>
<xslt:template match="ROOT/LIST/BIRTH_DATE/birth_month">
<birth_month>
<xslt:apply-templates />
</birth_month>
</xslt:template>
<xslt:template match="ROOT/LIST/BIRTH_DATE/birth_day">
<birth_day>
<xslt:apply-templates />
</birth_day>
</xslt:template>
<xslt:template match="ROOT/LIST/BIRTH_DATE/birth_year">
<birth_year>
<xslt:apply-templates />
</birth_year>
</xslt:template>
<xslt:template match="ROOT/LIST/BIRTH_DATE/birth_city">
<birth_city>
<xslt:apply-templates />
</birth_city>
</xslt:template>
<xslt:template match="ROOT/LIST/BIRTH_DATE/birth_state">
<birth_state>
<xslt:apply-templates />
</birth_state>
</xslt:template>
<xslt:template match="ROOT/LIST/BIRTH_DATE/birth_nation">
<birth_nation>
<xslt:apply-templates />
</birth_nation>
</xslt:template>
<xslt:template match="ROOT/LIST/EDUCATION/Degree">
<degree>
<xslt:apply-templates />
</degree>
</xslt:template>
<xslt:template match="ROOT/LIST/EDUCATION/School">
<school>
<xslt:apply-templates />
</school>
</xslt:template>
<xslt:template match="ROOT/LIST/EDUCATION/DegreeYear">
<degreeyear>
<xslt:apply-templates />
</degreeyear>
</xslt:template>
</xslt:stylesheet>

---------------------------------
XSD SCHEMA:
<xsd:schema id="ROOT" xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">

<xsd:annotation>
<xsd:appinfo>
<sql:relationship name="LIST_NAME"
parent="LIST" parent-key="personid"
child="NAME" child-key="personid" />
<sql:relationship name="LIST_MAILING_ADDRESS"
parent="LIST" parent-key="personid"
child="MAILING_ADDRESS" child-key="personid" />
<sql:relationship name="LIST_BIRTH_DATE"
parent="LIST" parent-key="personid"
child="BIRTH_DATE" child-key="personid" />
<sql:relationship name="LIST_EDUCATION"
parent="LIST" parent-key="personid"
child="EDUCATION" child-key="personid" />
</xsd:appinfo>
</xsd:annotation>


<xsd:element name="ROOT">
<xsd:complexType>
<xsd:choice maxOccurs="unbounded">
<xsd:element name="LIST" sql:relation="LIST">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="personid" type="xsd:string" minOccurs="0" sql:relation="personid"/>
<xsd:element name="last_upd" type="xsd:string" minOccurs="0" sql:relation="last_upd"/>
<xsd:element name="occ_desc" type="xsd:string" minOccurs="0" sql:relation="occ_desc"/>
<xsd:element name="gender" type="xsd:string" minOccurs="0" sql:relation="gender"/>
<xsd:element name="politics" type="xsd:string" minOccurs="0" sql:relation="politics"/>
<xsd:element name="religion" type="xsd:string" minOccurs="0" sql:relation="religion"/>
<xsd:element name="PAR" type="xsd:string" minOccurs="0" sql:relation="par"/>
<xsd:element name="AVO" type="xsd:string" minOccurs="0" "avo"/>
<xsd:element name="CAR" type="xsd:string" minOccurs="0" "car"/>
<xsd:element name="NAME" minOccurs="0" maxOccurs="unbounded" sql:relation="NAME" sql:relationship="LIST_NAME">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="name_prefix" type="xsd:string" minOccurs="0" sql:relation="name_prefix"/>
<xsd:element name="first_name" type="xsd:string" minOccurs="0" sql:relation="first_name"/>
<xsd:element name="middle_name" type="xsd:string" minOccurs="0" sql:relation="middle_name"/>
<xsd:element name="last_name" type="xsd:string" minOccurs="0" sql:relation="last_name"/>
<xsd:element name="name_suffix" type="xsd:string" minOccurs="0" sql:relation="name_suffix"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="MAILING_ADDRESS" minOccurs="0" maxOccurs="unbounded" sql:relation="MAILING_ADDRESS" sql:relationship="LIST_MAILING_ADDRESS">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="street1" type="xsd:string" minOccurs="0" sql:relation="street1" />
<xsd:element name="street2" type="xsd:string" minOccurs="0" sql:relation="street2" />
<xsd:element name="street3" type="xsd:string" minOccurs="0" sql:relation="street3" />
<xsd:element name="city" type="xsd:string" minOccurs="0" sql:relation="city"/>
<xsd:element name="pr_urb" type="xsd:string" minOccurs="0" sql:relation="pr_urb"/>
<xsd:element name="state_province" type="xsd:string" minOccurs="0" sql:relation="state_province"/>
<xsd:element name="zip" type="xsd:string" minOccurs="0" sql:relation="zip"/>
<xsd:element name="country" type="xsd:string" minOccurs="0" sql:relation="country"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="BIRTH_DATE" minOccurs="0" maxOccurs="unbounded" sql:relation="BIRTH_DATE" sql:relationship="LIST_BIRTH_DATE">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="birth_month" type="xsd:string" minOccurs="0" sql:relation="birth_month"/>
<xsd:element name="birth_day" type="xsd:string" minOccurs="0" sql:relation="birth_day"/>
<xsd:element name="birth_year" type="xsd:string" minOccurs="0" sql:relation="birth_year"/>
<xsd:element name="birth_city" type="xsd:string" minOccurs="0" sql:relation="birth_city"/>
<xsd:element name="birth_state" type="xsd:string" minOccurs="0" sql:relation="birth_state"/>
<xsd:element name="birth_nation" type="xsd:string" minOccurs="0" sql:relation="birth_nation" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="EDUCATION" minOccurs="0" maxOccurs="unbounded" sql:relation="EDUCATION" sql:relationship="LIST_EDUCATION">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Degree" type="xsd:string" minOccurs="0" sql:relation="degree"/>
<xsd:element name="School" type="xsd:string" minOccurs="0" sql:relation="school"/>
<xsd:element name="DegreeYear" type="xsd:string" minOccurs="0" sql:relation="degreenyear"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:choice>
</xsd:complexType>
</xsd:element>
</xsd:schema>
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site