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

From: Graeme Malcolm \(Content Master Ltd.\) (graemem_cm_at_hotmail.com)
Date: 03/25/04 

Date: Thu, 25 Mar 2004 10:59:28 -0000

Your best bet (imho) world be to apply an XSLT transformation to the data
that groups all of the required fields into a single <Customer> element.
Then import the transformed data using a schema that describes the new
"flatter" structure.

Cheers,
Graeme 

-- 
Graeme Malcolm
Principal Technologist
Content Master Ltd.
"Darren" <darrengaylor@hotmail.com> wrote in message
news:33A25392-A281-4B22-8D2E-828DAF253F79@microsoft.com...
> 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</Stat
e><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>