Stuck on Schema - don't know how to set up non fields

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Darren (darrengaylor_at_hotmail.com)
Date: 03/25/04 

Date: Wed, 24 Mar 2004 20:21:08 -0800

I am trying to import xml into sql server 2000 for the first time. I have installed the bulkload component, but am having a slight problem with the schema - my fields don't get populated with anything but the personid because i don't know how to define some of the elements.

I have a CustInfo table that has the following fields:
personid int
last_name char(20)
first_name char(15)
state char(2)
zip5 char(5)

I am receiving xml data that appears like this:
<?xml version="1.0" encoding="windows-1252"?><ROOT><BIOG><personid>1</personid><NAME><first_name>Kim</first_name><last_name>Clark</last_name></NAME><ADDRESS><State>AZ</State><Zip>85248</Zip></ADDRESS></BIOG><BIOG><personid>3</personid><NAME><first_name>John</first_name><last_name>Smith</last_name></NAME><ADDRESS><State>CA/State><Zip>90210</Zip></ADDRESS></BIOG></ROOT>

I have defined the following schema - but i do not know how to define NAME and ADDRESS since these are not fields that I put into my database - but I put the nested data that is under them into the database. Any help would be appreciated. Thank you.

<?xml version="1.0" ?><Schema xmlns="urn:schemas-microsoft-com:xml-data"
        xmlns:dt="urn:schemas-microsoft-com:xml:datatypes"
        xmlns:sql="urn:schemas-microsoft-com:xml-sql" ><ElementType name="personid" dt:type="int" /><ElementType name="first_name" dt:type="string" /><ElementType name="last_name" dt:type="string" /><ElementType name="state" dt:type="string" /><ElementType name="zip5" dt:type="string" /><ElementType name="ROOT" sql:is-constant="1"><element type="BIOG" /></ElementType><ElementType name="BIOG" sql:relation="STAT_CUST" ><element type="personid" sql:field="personid" /><element type="first_name" sql:field="first_name" /><element type="last_name" sql:field="last_name" /><element type="state" sql:field="state" /><element type="zip5" sql:field="zip5" /></ElementType></Schema>