Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

INSERT data from XML File into SQL Table -- SQL SERVER 2000

Posted on 2009-04-14
6
Medium Priority
?
535 Views
Last Modified: 2012-05-06
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.
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>

Open in new window

0
Comment
Question by:dbbishop
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 4

Accepted Solution

by:
jason_woods earned 2000 total points
ID: 24142283
0
 
LVL 15

Author Comment

by:dbbishop
ID: 24142365
Is there a way to do this completely within SQL with T-SQL and without the mapping file? It seems like I recall that is how I did it before (without the mapping file).
0
 
LVL 4

Expert Comment

by:jason_woods
ID: 24142423
Possibly, but that doesn't sound right. T-SQL doesn't usually have access to files (as far as I know).
0
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
LVL 15

Author Comment

by:dbbishop
ID: 24142490
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?
0
 
LVL 4

Assisted Solution

by:jason_woods
jason_woods earned 2000 total points
ID: 24143070
You can use bigint directly. Datetime will be at zero hour of the date.
0
 
LVL 15

Author Closing Comment

by:dbbishop
ID: 31570148
Thanks. I got it to work.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

704 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question