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.
| Author |
Topic |
|
dgaylor
Yak Posting Veteran
54 Posts |
 Posted - 2004-03-25 : 11:08:06
|
| 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 intlast_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="CustInfo" ><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> |
|
|
|
|
|
XML Bulk Load - schema problem