D B
asked on
INSERT data from XML File into SQL Table -- SQL SERVER 2000
I am using SQL Server 2000 and I have the following DML for a table I've created, followed by a snippet of the XML data file. As you can see from the data, there are multiple <INDIVIDUAL_ORDER> elements. I want to load this data into the table XML_TEST. The XML file is in a folder that the server has access to. It has been several years since I've done anything with XML and rememeber I researched forever to figure it out back then (no EE at the time) and don't want to repeat the experience.
I would greatly appreciate any help in coming up with the appropriate code to produce the required results.
I would greatly appreciate any help in coming up with the appropriate code to produce the required results.
CREATE TABLE XML_TEST
(
SHIP_DATE DATETIME NULL,
PRODUCT_ID CHAR(10) NOT NULL,
VNDR_PART VARCHAR(25) NOT NULL,
[DESCRIPTION] VARCHAR(50) NOT NULL,
SHIP_TO CHAR(6) NOT NULL,
Z_NAME VARCHAR(25) NOT NULL,
CUST_NAME VARCHAR(25) NOT NULL,
ADDRESS1 VARCHAR(50) NOT NULL,
ADDRESS2 VARCHAR(50) NULL,
ADDRESS3 VARCHAR(50) NULL,
CITY VARCHAR(30) NOT NULL,
STATE CHAR(2) NOT NULL,
ZIP VARCHAR(10) NOT NULL,
CUST_TEL BIGINT NOT NULL,
SERIAL_NUM VARCHAR(20) NOT NULL,
CUSTOMER_PO VARCHAR(10) NULL
)
<?xml version="1.0" encoding="UTF-8"?>
<COMPANY_LOGISTICS_DATA xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<INDIVIDUAL_ORDER>
<SHIP_DATE>2009-04-07</SHIP_DATE>
<PRODUCT_ID>0000217052</PRODUCT_ID>
<VNDR_PART>BHS2WRF(322)-2-SER</VNDR_PART>
<DESCRIPTION>2WIRE 2701 FILTERED KIT-2-Seri</DESCRIPTION>
<SHIP_TO>000618</SHIP_TO>
<Z_NAME>XXXXX XXXXXXXXXX</Z_NAME>
<CUST_NAME>XXXXX XXXXXXXXXX</CUST_NAME>
<ADDRESS1>1234 MAIN ST</ADDRESS1>
<ADDRESS2></ADDRESS2>
<ADDRESS3></ADDRESS3>
<CITY>BLAND </CITY>
<STATE>VA</STATE>
<ZIP>24315-0000</ZIP>
<CUST_TEL>1234567890</CUST_TEL>
<SERIAL_NUM>123456789012</SERIAL_NUM>
<CUSTOMER_PO>0009443938</CUSTOMER_PO>
</INDIVIDUAL_ORDER>
<INDIVIDUAL_ORDER>
<SHIP_DATE>2009-04-07</SHIP_DATE>
<PRODUCT_ID>0000217052</PRODUCT_ID>
<VNDR_PART>BHS2WRF(322)-2-SER</VNDR_PART>
<DESCRIPTION>2WIRE 2701 FILTERED KIT-2-Seri</DESCRIPTION>
<SHIP_TO>000639</SHIP_TO>
<Z_NAME>ZZZZZZZZ ZZZZZZZZZZZZ</Z_NAME>
<CUST_NAME>ZZZZZZZZ ZZZZZZZZZZZZ</CUST_NAME>
<ADDRESS1>9876 ANY ST</ADDRESS1>
<ADDRESS2></ADDRESS2>
<ADDRESS3></ADDRESS3>
<CITY>MIAMI </CITY>
<STATE>FL</STATE>
<ZIP>33513-0000</ZIP>
<CUST_TEL>9876543210</CUST_TEL>
<SERIAL_NUM>940919017604</SERIAL_NUM>
<CUSTOMER_PO>0009443673</CUSTOMER_PO>
</INDIVIDUAL_ORDER>
</COMPANY_LOGISTICS_DATA>
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Possibly, but that doesn't sound right. T-SQL doesn't usually have access to files (as far as I know).
ASKER
In the code sample, it includes:
<ElementType name="CustomerId" dt:type="int" />
What would I use for dt:type=""
to map the BIGINT and the DATETIME (it is actually just a date without a time component in the XML file) datatypes?
<ElementType name="CustomerId" dt:type="int" />
What would I use for dt:type=""
to map the BIGINT and the DATETIME (it is actually just a date without a time component in the XML file) datatypes?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks. I got it to work.
ASKER