importing XML doc into SQL Server

Christine Mccormick

Hello, I am trying to import an XML document into SQL Server. I have tried
DTS, bulk loading into SQL server and anything else I can think of...

The only thing that has worked slightly is :
Dim objSQLConn As New SqlConnection(Constants.ConnectionString)

Dim objAdapter As SqlDataAdapter

Dim objDataRow, objDBRow As DataRow

Dim objDSXML As New DataSet()

Dim child As Xml.XmlNode

Dim objDSDBTable As New DataSet("tblStandardstest")

Dim ObjCmdBuilder As SqlCommandBuilder

objDSXML.ReadXml("c:/inetpub/wwwroot/olpt7/ACH_STATE_SUBJECT_Ohio_2001_Engli
sh.xml")

objSQLConn.Open()

objAdapter = New SqlDataAdapter("SELECT Benchmark, Grade,RefNum FROM
Standards", objSQLConn)objAdapter.Fill(objDSDBTable, "tblStandardstest")

For Each objDataRow In objDSXML.Tables("ACH_BENCHMARK").Rows

With objDSDBTable.Tables(0)

objDBRow = .NewRow()

objDBRow(0) = objDataRow("ACH_BENCHMARK_TEXT")

objDBRow(1) = objDataRow("ACH_GRADE_RANGE")

objDBRow(2) = objDataRow("ACH_STATE_REF_NUM")

..Rows.Add(objDBRow)

End With

ObjCmdBuilder = New SqlCommandBuilder(objAdapter)

objAdapter.Update(objDSDBTable, "tblstandardstest")

Next

objSQLConn.Close()

This only gets the one level. I need all levels to be placed in a SQL Server
Database in relational tables. Please help me!!!!!!!!!!!!!!!!!!!!!!

**********************SCHEMA********************** ******

<?xml version="1.0" encoding="utf-8"?>



<XSDDesignerLayout layoutVersion="1" viewPortLeft="-10553"
viewPortTop="3344">

<ACH_STATE_SUBJECT_XmlElement left="-1561" top="450" width="15557"
height="3863" selected="0" zOrder="1" index="0">

<ACH_BENCHMARK_XmlElement left="-688" top="5583" width="13811" height="2963"
selected="0" zOrder="2" index="5">

<ACH_HEADERS_XmlElement left="-9553" top="9816" width="5292" height="2963"
selected="0" zOrder="5" index="4" />

<ACH_COMPENDIX_ALIGNMENT_XmlElement left="3466" top="9816" width="12065"
height="2963" selected="0" zOrder="7" index="5">

<ACH_COMPENDIX_SUBJ_XmlElement left="-2991" top="14049" width="5292"
height="2963" selected="0" zOrder="9" index="0" />

<ACH_COMPENDIX_TRIPLET_XmlElement left="3571" top="14049" width="5292"
height="2963" selected="0" zOrder="11" index="1" />

<ACH_COMPENDIX_VOCAB_XmlElement left="13414" top="14049" width="5292"
height="2963" selected="0" zOrder="13" index="2">

<ACH_COMPENDIX_VOCAB_LETTER_XmlElement left="10133" top="18282" width="5292"
height="2963" selected="0" zOrder="15" index="0" />

<ACH_COMPENDIX_VOCAB_WORD_XmlElement left="16695" top="18282" width="5292"
height="2963" selected="0" zOrder="17" index="1" />

</ACH_COMPENDIX_VOCAB_XmlElement>

</ACH_COMPENDIX_ALIGNMENT_XmlElement>

</ACH_BENCHMARK_XmlElement>

</ACH_STATE_SUBJECT_XmlElement>

<NewDataSet_XmlElement left="33445" top="635" width="5292" height="2963"
selected="0" zOrder="19" index="1">

<ref_x003D_ACH_STATE_SUBJECT_XmlElement left="33445" top="4868" width="5292"
height="2963" selected="0" zOrder="20" index="0">

<ACH_BENCHMARK_XmlElement left="33445" top="9101" width="5292" height="2963"
selected="0" zOrder="22" index="5">

<ACH_HEADERS_XmlElement left="20321" top="13334" width="5292" height="2963"
selected="0" zOrder="24" index="4" />

<ACH_COMPENDIX_ALIGNMENT_XmlElement left="36726" top="13334" width="5292"
height="2963" selected="0" zOrder="26" index="5">

<ACH_COMPENDIX_SUBJ_XmlElement left="26883" top="17567" width="5292"
height="2963" selected="0" zOrder="28" index="0" />

<ACH_COMPENDIX_TRIPLET_XmlElement left="33445" top="17567" width="5292"
height="2963" selected="0" zOrder="30" index="1" />

<ACH_COMPENDIX_VOCAB_XmlElement left="43288" top="17567" width="5292"
height="2963" selected="0" zOrder="32" index="2">

<ACH_COMPENDIX_VOCAB_LETTER_XmlElement left="40007" top="21800" width="5292"
height="2963" selected="0" zOrder="34" index="0" />

<ACH_COMPENDIX_VOCAB_WORD_XmlElement left="46569" top="21800" width="5292"
height="2963" selected="0" zOrder="36" index="1" />

</ACH_COMPENDIX_VOCAB_XmlElement>

</ACH_COMPENDIX_ALIGNMENT_XmlElement>

</ACH_BENCHMARK_XmlElement>

</ref_x003D_ACH_STATE_SUBJECT_XmlElement>

</NewDataSet_XmlElement>

</XSDDesignerLayout>

*************************************END SCHEMA************************

Nov 11 '05 #1

Subscribe Post Reply

4121

Christoph Schittko [MVP]

Christine,

Take a look at SQLXML [0]. You can import the XML data either via
Updategrams or via Bulkload (available through COM interop)

I hope this link is getting you started. Feel free to come back once you
have more questions.

--
HTH
Christoph Schittko [MVP]
Software Architect, .NET Mentor
[0]
http://msdn.microsoft.com/library/de...entid=28001300

"Christine Mccormick" <cm*******@earthlink.net> wrote in message
news:tF********************@newsread2.prod.itd.ear thlink.net...

Hello, I am trying to import an XML document into SQL Server. I have tried
DTS, bulk loading into SQL server and anything else I can think of...

The only thing that has worked slightly is :
Dim objSQLConn As New SqlConnection(Constants.ConnectionString)

Dim objAdapter As SqlDataAdapter

Dim objDataRow, objDBRow As DataRow

Dim objDSXML As New DataSet()

Dim child As Xml.XmlNode

Dim objDSDBTable As New DataSet("tblStandardstest")

Dim ObjCmdBuilder As SqlCommandBuilder

objDSXML.ReadXml("c:/inetpub/wwwroot/olpt7/ACH_STATE_SUBJECT_Ohio_2001_Engli sh.xml")

objSQLConn.Open()

objAdapter = New SqlDataAdapter("SELECT Benchmark, Grade,RefNum FROM
Standards", objSQLConn)objAdapter.Fill(objDSDBTable, "tblStandardstest")

For Each objDataRow In objDSXML.Tables("ACH_BENCHMARK").Rows

With objDSDBTable.Tables(0)

objDBRow = .NewRow()

objDBRow(0) = objDataRow("ACH_BENCHMARK_TEXT")

objDBRow(1) = objDataRow("ACH_GRADE_RANGE")

objDBRow(2) = objDataRow("ACH_STATE_REF_NUM")

.Rows.Add(objDBRow)

End With

ObjCmdBuilder = New SqlCommandBuilder(objAdapter)

objAdapter.Update(objDSDBTable, "tblstandardstest")

Next

objSQLConn.Close()

This only gets the one level. I need all levels to be placed in a SQL Server Database in relational tables. Please help me!!!!!!!!!!!!!!!!!!!!!!

**********************SCHEMA********************** ******

<?xml version="1.0" encoding="utf-8"?>



<XSDDesignerLayout layoutVersion="1" viewPortLeft="-10553"
viewPortTop="3344">

<ACH_STATE_SUBJECT_XmlElement left="-1561" top="450" width="15557"
height="3863" selected="0" zOrder="1" index="0">

<ACH_BENCHMARK_XmlElement left="-688" top="5583" width="13811" height="2963" selected="0" zOrder="2" index="5">

<ACH_HEADERS_XmlElement left="-9553" top="9816" width="5292" height="2963"
selected="0" zOrder="5" index="4" />

<ACH_COMPENDIX_ALIGNMENT_XmlElement left="3466" top="9816" width="12065"
height="2963" selected="0" zOrder="7" index="5">

<ACH_COMPENDIX_SUBJ_XmlElement left="-2991" top="14049" width="5292"
height="2963" selected="0" zOrder="9" index="0" />

<ACH_COMPENDIX_TRIPLET_XmlElement left="3571" top="14049" width="5292"
height="2963" selected="0" zOrder="11" index="1" />

<ACH_COMPENDIX_VOCAB_XmlElement left="13414" top="14049" width="5292"
height="2963" selected="0" zOrder="13" index="2">

<ACH_COMPENDIX_VOCAB_LETTER_XmlElement left="10133" top="18282" width="5292" height="2963" selected="0" zOrder="15" index="0" />

<ACH_COMPENDIX_VOCAB_WORD_XmlElement left="16695" top="18282" width="5292"
height="2963" selected="0" zOrder="17" index="1" />

</ACH_COMPENDIX_VOCAB_XmlElement>

</ACH_COMPENDIX_ALIGNMENT_XmlElement>

</ACH_BENCHMARK_XmlElement>

</ACH_STATE_SUBJECT_XmlElement>

<NewDataSet_XmlElement left="33445" top="635" width="5292" height="2963"
selected="0" zOrder="19" index="1">

<ref_x003D_ACH_STATE_SUBJECT_XmlElement left="33445" top="4868" width="5292" height="2963" selected="0" zOrder="20" index="0">

<ACH_BENCHMARK_XmlElement left="33445" top="9101" width="5292" height="2963" selected="0" zOrder="22" index="5">

<ACH_HEADERS_XmlElement left="20321" top="13334" width="5292" height="2963" selected="0" zOrder="24" index="4" />

<ACH_COMPENDIX_ALIGNMENT_XmlElement left="36726" top="13334" width="5292"
height="2963" selected="0" zOrder="26" index="5">

<ACH_COMPENDIX_SUBJ_XmlElement left="26883" top="17567" width="5292"
height="2963" selected="0" zOrder="28" index="0" />

<ACH_COMPENDIX_TRIPLET_XmlElement left="33445" top="17567" width="5292"
height="2963" selected="0" zOrder="30" index="1" />

<ACH_COMPENDIX_VOCAB_XmlElement left="43288" top="17567" width="5292"
height="2963" selected="0" zOrder="32" index="2">

<ACH_COMPENDIX_VOCAB_LETTER_XmlElement left="40007" top="21800" width="5292" height="2963" selected="0" zOrder="34" index="0" />

<ACH_COMPENDIX_VOCAB_WORD_XmlElement left="46569" top="21800" width="5292"
height="2963" selected="0" zOrder="36" index="1" />

</ACH_COMPENDIX_VOCAB_XmlElement>

</ACH_COMPENDIX_ALIGNMENT_XmlElement>

</ACH_BENCHMARK_XmlElement>

</ref_x003D_ACH_STATE_SUBJECT_XmlElement>

</NewDataSet_XmlElement>

</XSDDesignerLayout>

*************************************END SCHEMA************************

Nov 11 '05 #2

Similar topics

Hyper-efficient Text Importing

by: Awah Teh | last post by:

I am working on a project that involves importing IIS Log files into a SQL Server database (and these logfiles are big --> Some up to 2GB in size). Up until now I thought that DTS or the BULK...

ASP / Active Server Pages

Importing Tables Using MySQL Control Center

by: Campbell's List | last post by:

Hi, I'm completely new to MySql and need help importing tables from Access or Dbase. I am using Dreamweaver MX to create a data-driven web site. With the VPS hosting plan we're on, our remote...

MySQL Database

Best way of importing Excel to SQL Server

by: Little PussyCat | last post by:

Hello, I nee to write something that will transfer excel data into an SQL Server table. I have for another database application I wrote have it importing Excel spreadsheet data using cell by...

Microsoft SQL Server

Cannot find server or DNS Error when importing data

by: Daryl Zavier | last post by:

Hi Gurus, Please forgive me if my question is dumb cause this is my first web application I've done. Currently in my web application there is a button to click which will fire off a DTS event...

.NET Framework

Importing comma delimited file

by: Elmo Watson | last post by:

I've been asked to develop a semi-automated type situation where we have a database table (sql server) and periodically, there will be a comma delimited file from which we need to import the data,...

ASP / Active Server Pages

automatic namespace importing

by: Natan | last post by:

When you create and aspx page, this is generated by default: using System; using System.Collections; using System.Collections.Specialized; using System.Configuration; using System.Text; using...

ASP.NET

Importing data from across the web? XML?

by: Roy | last post by:

Could someone point me in the right direction here? The current method of importing new data into our db goes something like this: 1.) txt files received via email 2.) employees clean data and...

ASP.NET

Standard Method for Importing/Exporting to DB

by: Snozz | last post by:

The short of it: If you needed to import a CSV file of a certain structure on a regular basis(say 32 csv files, each to one a table in 32 databases), what would be your first instinct on how to...

Visual Basic .NET

Having Problem While Importing a Text File

by: panic attack | last post by:

Hello everbody, Our system is using Sql Server 2000 on Windows XP / Windows 2000 We have a text file needs to be imported into Sql Server 2000 as a table. But we are facing a problem which is,...

Microsoft SQL Server

Importing data into SQL Server 2005 via ODBC

by: Chloe C | last post by:

Hi I've got an Ingres database of some 200 tables which I need to import every night into SQL Server 2005 for use by Reporting Services. Most of the tables will come across unchanged (a few need...

Microsoft SQL Server

Access Europe: Command bars, the Access Shortcut Tool and a simple Audit Log - Wed 3 April

by: isladogs | last post by:

The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

General

One-click Importing Excel Data into a*Database

by: ryjfgjl | last post by:

In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...

Microsoft Excel

Easy Steps to Fix "Canon Printer Won't Connect to WiFi Network"

by: taylorcarr | last post by:

A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...

General

How to turn on java script in a villaon keypad mobile phone

by: Charles Arthur | last post by:

How do i turn on java script on a villaon, callus and itel keypad mobile phone

Java

Batch import of multiple excel files into the database

by: ryjfgjl | last post by:

If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...

Data Management

Merging data from multiple Excel files

by: ryjfgjl | last post by:

In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...

Data Management

Navigating the Data Structures and Algorithms (DSA)

by: BarryA | last post by:

What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...

Algorithms / Advanced Math

Looking to do Android software development, any suggestions? Is flutter better?

by: nemocccc | last post by:

hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?

General

How to build RAID in BIOS?

by: Hystou | last post by:

There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

Computer Hardware