Importing XML into SQL using DTS

I need to import large XML files into an SQL table.
My XML experience is minimal...
My current DTS script can import xml files which are more structured (using
NODES) and works fine. How ever i need to modify it to look at Attributes
instead of nodes.
It looks like this:
'************************************************* *********************
' Visual Basic ActiveX Script
'************************************************* ***********************
Function Main()
Dim objXMLDOM
Dim objNodes
Dim objBookNode
Dim objADORS
Dim objADOCnn
Const adOpenKeyset = 1
Const adLockOptimistic = 3
Set objXMLDOM = CreateObject("MSXML2.DOMDocument.4.0")
objXMLDOM.async = False
objXMLDOM.validateOnParse = False
'No error handling done
objXMLDOM.load
"U:\2-Data\RStation\Unprocessed\bns_usage_2005-05-16_bns2ha.xml"
Set objNodes = objXMLDOM.selectNodes("/Books/Book")
Set objADOCnn = CreateObject("ADODB.Connection")
Set objADORS = CreateObject("ADODB.Recordset")
objADOCnn.Open
"PROVIDER=SQLOLEDB;SERVER=UKIW0004921G\LOCAL;UID=s a;PWD=bigbird;DATABASE=ImportXML;"
objADORS.Open "SELECT * FROM tmpImportXML WHERE 1 = 2", objADOCnn,
adOpenKeyset, adLockOptimistic
For Each objBookNode In objNodes
With objADORS
.AddNew
.fields("BookTitle") = objBookNode.selectSingleNode("Title").nodeTypedVal ue
.fields("Publisher") =
objBookNode.selectSingleNode("Publisher").nodeType dValue
.fields("DateOfPurchase") =
objBookNode.selectSingleNode("DateOfPurchase").nod eTypedValue
.Update
End With
Next
objADORS.Close
objADOCnn.Close
Main = DTSTaskExecResult_Success
End Function
#################
How do i modify it to look at an XML file structured using Attributes?
...XML File looks like
The xml structure looks like this:
<?xml version="1.0" encoding="utf-8"?>
<usageFile source="abc" countRetrievals="12345" countSearches="0"
fileStart="2005-05-16T05:46:36" fileEnd="2005-05-16T07:00:00">
<BookTitle="abc123" Publisher="abcdef"
DateOfPurchase="2005-05-16T05:45:36"/>
Thanks for the help
Did you try to use '@.Publisher' instead of 'Publisher' in your path
expression?
Best regards
Michael
"Fec" <Fec@.discussions.microsoft.com> wrote in message
news:E454BA66-8786-403D-BC80-406028CCFDAE@.microsoft.com...
>I need to import large XML files into an SQL table.
> My XML experience is minimal...
> My current DTS script can import xml files which are more structured
> (using
> NODES) and works fine. How ever i need to modify it to look at Attributes
> instead of nodes.
> It looks like this:
> '************************************************* *********************
> ' Visual Basic ActiveX Script
> '************************************************* ***********************
> Function Main()
> Dim objXMLDOM
> Dim objNodes
> Dim objBookNode
> Dim objADORS
> Dim objADOCnn
> Const adOpenKeyset = 1
> Const adLockOptimistic = 3
> Set objXMLDOM = CreateObject("MSXML2.DOMDocument.4.0")
> objXMLDOM.async = False
> objXMLDOM.validateOnParse = False
> 'No error handling done
> objXMLDOM.load
> "U:\2-Data\RStation\Unprocessed\bns_usage_2005-05-16_bns2ha.xml"
> Set objNodes = objXMLDOM.selectNodes("/Books/Book")
> Set objADOCnn = CreateObject("ADODB.Connection")
> Set objADORS = CreateObject("ADODB.Recordset")
> objADOCnn.Open
> "PROVIDER=SQLOLEDB;SERVER=UKIW0004921G\LOCAL;UID=s a;PWD=bigbird;DATABASE=ImportXML;"
> objADORS.Open "SELECT * FROM tmpImportXML WHERE 1 = 2", objADOCnn,
> adOpenKeyset, adLockOptimistic
> For Each objBookNode In objNodes
> With objADORS
> .AddNew
> .fields("BookTitle") =
> objBookNode.selectSingleNode("Title").nodeTypedVal ue
> .fields("Publisher") =
> objBookNode.selectSingleNode("Publisher").nodeType dValue
> .fields("DateOfPurchase") =
> objBookNode.selectSingleNode("DateOfPurchase").nod eTypedValue
> .Update
> End With
> Next
> objADORS.Close
> objADOCnn.Close
> Main = DTSTaskExecResult_Success
> End Function
> #################
> How do i modify it to look at an XML file structured using Attributes?
> ...XML File looks like
> The xml structure looks like this:
> <?xml version="1.0" encoding="utf-8"?>
> <usageFile source="abc" countRetrievals="12345" countSearches="0"
> fileStart="2005-05-16T05:46:36" fileEnd="2005-05-16T07:00:00">
> <BookTitle="abc123" Publisher="abcdef"
> DateOfPurchase="2005-05-16T05:45:36"/>
> --
>
> Thanks for the help
|||Try to use attributes property on Dom nodes to get the values of attributes:
http://msdn.microsoft.com/library/de...65757ceb24.asp
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"Fec" <Fec@.discussions.microsoft.com> wrote in message
news:E454BA66-8786-403D-BC80-406028CCFDAE@.microsoft.com...
>I need to import large XML files into an SQL table.
> My XML experience is minimal...
> My current DTS script can import xml files which are more structured
> (using
> NODES) and works fine. How ever i need to modify it to look at Attributes
> instead of nodes.
> It looks like this:
> '************************************************* *********************
> ' Visual Basic ActiveX Script
> '************************************************* ***********************
> Function Main()
> Dim objXMLDOM
> Dim objNodes
> Dim objBookNode
> Dim objADORS
> Dim objADOCnn
> Const adOpenKeyset = 1
> Const adLockOptimistic = 3
> Set objXMLDOM = CreateObject("MSXML2.DOMDocument.4.0")
> objXMLDOM.async = False
> objXMLDOM.validateOnParse = False
> 'No error handling done
> objXMLDOM.load
> "U:\2-Data\RStation\Unprocessed\bns_usage_2005-05-16_bns2ha.xml"
> Set objNodes = objXMLDOM.selectNodes("/Books/Book")
> Set objADOCnn = CreateObject("ADODB.Connection")
> Set objADORS = CreateObject("ADODB.Recordset")
> objADOCnn.Open
> "PROVIDER=SQLOLEDB;SERVER=UKIW0004921G\LOCAL;UID=s a;PWD=bigbird;DATABASE=ImportXML;"
> objADORS.Open "SELECT * FROM tmpImportXML WHERE 1 = 2", objADOCnn,
> adOpenKeyset, adLockOptimistic
> For Each objBookNode In objNodes
> With objADORS
> .AddNew
> .fields("BookTitle") =
> objBookNode.selectSingleNode("Title").nodeTypedVal ue
> .fields("Publisher") =
> objBookNode.selectSingleNode("Publisher").nodeType dValue
> .fields("DateOfPurchase") =
> objBookNode.selectSingleNode("DateOfPurchase").nod eTypedValue
> .Update
> End With
> Next
> objADORS.Close
> objADOCnn.Close
> Main = DTSTaskExecResult_Success
> End Function
> #################
> How do i modify it to look at an XML file structured using Attributes?
> ...XML File looks like
> The xml structure looks like this:
> <?xml version="1.0" encoding="utf-8"?>
> <usageFile source="abc" countRetrievals="12345" countSearches="0"
> fileStart="2005-05-16T05:46:36" fileEnd="2005-05-16T07:00:00">
> <BookTitle="abc123" Publisher="abcdef"
> DateOfPurchase="2005-05-16T05:45:36"/>
> --
>
> Thanks for the help

importing from oracle to an adp database

hi,

this is my current set up:

sql server 2005 express edition,
sql server management studio express,
oracle tables (original source data currently linked through odbc)
adp database file (new db where i'm trying to import the above oracle tables)

i need to import (or make copies) on a weekly/daily basis, of several oracle tables into a new adp database.

what is the fastest option? some of these tables hold over 1m records.

i have also heard of "linked servers" and "dts", but would like some experts advice before starting looking into this.

many thanks
luzippu

Hi luzippu,

Since you're going to be doing this on a regular basis I would recomend using the following:

Linked Server to the Oracle database.|||

Mike,

thank you for your feedback, it was nice to see a reply after many views...

Since I've posted this query I've had chance to find out more about the different versions.

In addition I have now the option to use MS SQL Server 7.0, thus DTS and the import scheduler facility.

Do you think this is the best option? Or shall I go with your solution above?

If the latter, could you give me an idea on what a Stored Procedure would look like to copy let's say:
ora_table1 to sql_table1 where create_date >= "01/01/2000"

Thank you
luzippu

sql

Importing Data into SQL 2005

I tried to import a table from SQL 2000 server to SQL 2005 server. Between
SQL 2000 servers I can choose to drop the current table if it exists at
destination. However in SQL Server Management Studio there is no option to
drop the existing table. Do I miss something here? How do you guys import
in this case? Thanks.
It gets worse. The "object transfer" component doesn't take indexes,
identity properties, constraints or pretty much anything except the raw
table structure. DTS was a good data migration utility and a decent ETL
platform. SSIS is a great ETL tool and a poor data migration tool.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"ME" <ME@.mail.com> wrote in message
news:ONJ1$SwGHHA.3540@.TK2MSFTNGP02.phx.gbl...
>I tried to import a table from SQL 2000 server to SQL 2005 server. Between
>SQL 2000 servers I can choose to drop the current table if it exists at
>destination. However in SQL Server Management Studio there is no option to
>drop the existing table. Do I miss something here? How do you guys import
>in this case? Thanks.
>
|||You are correct.
now it comes to fun part: to create SSIS package I must purchase license for
Visual Studio?
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:%23r$SwowGHHA.1264@.TK2MSFTNGP03.phx.gbl...
> It gets worse. The "object transfer" component doesn't take indexes,
> identity properties, constraints or pretty much anything except the raw
> table structure. DTS was a good data migration utility and a decent ETL
> platform. SSIS is a great ETL tool and a poor data migration tool.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "ME" <ME@.mail.com> wrote in message
> news:ONJ1$SwGHHA.3540@.TK2MSFTNGP02.phx.gbl...
>
|||SQL Server 2005 Developer edition is $50 and includes the BI workbench.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"ME" <ME@.mail.com> wrote in message
news:u0yaw2wGHHA.4904@.TK2MSFTNGP04.phx.gbl...
> You are correct.
> now it comes to fun part: to create SSIS package I must purchase license
> for Visual Studio?
>
> "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
> news:%23r$SwowGHHA.1264@.TK2MSFTNGP03.phx.gbl...
>

Importing Data into SQL 2005

I tried to import a table from SQL 2000 server to SQL 2005 server. Between
SQL 2000 servers I can choose to drop the current table if it exists at
destination. However in SQL Server Management Studio there is no option to
drop the existing table. Do I miss something here? How do you guys import
in this case? Thanks.It gets worse. The "object transfer" component doesn't take indexes,
identity properties, constraints or pretty much anything except the raw
table structure. DTS was a good data migration utility and a decent ETL
platform. SSIS is a great ETL tool and a poor data migration tool.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"ME" <ME@.mail.com> wrote in message
news:ONJ1$SwGHHA.3540@.TK2MSFTNGP02.phx.gbl...
>I tried to import a table from SQL 2000 server to SQL 2005 server. Between
>SQL 2000 servers I can choose to drop the current table if it exists at
>destination. However in SQL Server Management Studio there is no option to
>drop the existing table. Do I miss something here? How do you guys import
>in this case? Thanks.
>|||You are correct.
now it comes to fun part: to create SSIS package I must purchase license for
Visual Studio?
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:%23r$SwowGHHA.1264@.TK2MSFTNGP03.phx.gbl...
> It gets worse. The "object transfer" component doesn't take indexes,
> identity properties, constraints or pretty much anything except the raw
> table structure. DTS was a good data migration utility and a decent ETL
> platform. SSIS is a great ETL tool and a poor data migration tool.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "ME" <ME@.mail.com> wrote in message
> news:ONJ1$SwGHHA.3540@.TK2MSFTNGP02.phx.gbl...
>>I tried to import a table from SQL 2000 server to SQL 2005 server.
>>Between SQL 2000 servers I can choose to drop the current table if it
>>exists at destination. However in SQL Server Management Studio there is
>>no option to drop the existing table. Do I miss something here? How do
>>you guys import in this case? Thanks.
>|||SQL Server 2005 Developer edition is $50 and includes the BI workbench.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"ME" <ME@.mail.com> wrote in message
news:u0yaw2wGHHA.4904@.TK2MSFTNGP04.phx.gbl...
> You are correct.
> now it comes to fun part: to create SSIS package I must purchase license
> for Visual Studio?
>
> "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
> news:%23r$SwowGHHA.1264@.TK2MSFTNGP03.phx.gbl...
>> It gets worse. The "object transfer" component doesn't take indexes,
>> identity properties, constraints or pretty much anything except the raw
>> table structure. DTS was a good data migration utility and a decent ETL
>> platform. SSIS is a great ETL tool and a poor data migration tool.
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>>
>>
>> "ME" <ME@.mail.com> wrote in message
>> news:ONJ1$SwGHHA.3540@.TK2MSFTNGP02.phx.gbl...
>>I tried to import a table from SQL 2000 server to SQL 2005 server.
>>Between SQL 2000 servers I can choose to drop the current table if it
>>exists at destination. However in SQL Server Management Studio there is
>>no option to drop the existing table. Do I miss something here? How do
>>you guys import in this case? Thanks.
>>
>|||> now it comes to fun part: to create SSIS package I must purchase license for Visual Studio?
No, the Visual Studio IDE is shipped with SQL Server. It is called Business Intelligence Development
Studio.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ME" <ME@.mail.com> wrote in message news:u0yaw2wGHHA.4904@.TK2MSFTNGP04.phx.gbl...
> You are correct.
> now it comes to fun part: to create SSIS package I must purchase license for Visual Studio?
>
> "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
> news:%23r$SwowGHHA.1264@.TK2MSFTNGP03.phx.gbl...
>> It gets worse. The "object transfer" component doesn't take indexes, identity properties,
>> constraints or pretty much anything except the raw table structure. DTS was a good data
>> migration utility and a decent ETL platform. SSIS is a great ETL tool and a poor data migration
>> tool.
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>>
>>
>> "ME" <ME@.mail.com> wrote in message news:ONJ1$SwGHHA.3540@.TK2MSFTNGP02.phx.gbl...
>>I tried to import a table from SQL 2000 server to SQL 2005 server. Between SQL 2000 servers I can
>>choose to drop the current table if it exists at destination. However in SQL Server Management
>>Studio there is no option to drop the existing table. Do I miss something here? How do you guys
>>import in this case? Thanks.
>>
>

Importing Data into SQL 2005

I tried to import a table from SQL 2000 server to SQL 2005 server. Between
SQL 2000 servers I can choose to drop the current table if it exists at
destination. However in SQL Server Management Studio there is no option to
drop the existing table. Do I miss something here? How do you guys import
in this case? Thanks.It gets worse. The "object transfer" component doesn't take indexes,
identity properties, constraints or pretty much anything except the raw
table structure. DTS was a good data migration utility and a decent ETL
platform. SSIS is a great ETL tool and a poor data migration tool.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"ME" <ME@.mail.com> wrote in message
news:ONJ1$SwGHHA.3540@.TK2MSFTNGP02.phx.gbl...
>I tried to import a table from SQL 2000 server to SQL 2005 server. Between
>SQL 2000 servers I can choose to drop the current table if it exists at
>destination. However in SQL Server Management Studio there is no option to
>drop the existing table. Do I miss something here? How do you guys import
>in this case? Thanks.
>|||You are correct.
now it comes to fun part: to create SSIS package I must purchase license for
Visual Studio?
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:%23r$SwowGHHA.1264@.TK2MSFTNGP03.phx.gbl...
> It gets worse. The "object transfer" component doesn't take indexes,
> identity properties, constraints or pretty much anything except the raw
> table structure. DTS was a good data migration utility and a decent ETL
> platform. SSIS is a great ETL tool and a poor data migration tool.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "ME" <ME@.mail.com> wrote in message
> news:ONJ1$SwGHHA.3540@.TK2MSFTNGP02.phx.gbl...
>|||SQL Server 2005 Developer edition is $50 and includes the BI workbench.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"ME" <ME@.mail.com> wrote in message
news:u0yaw2wGHHA.4904@.TK2MSFTNGP04.phx.gbl...
> You are correct.
> now it comes to fun part: to create SSIS package I must purchase license
> for Visual Studio?
>
> "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
> news:%23r$SwowGHHA.1264@.TK2MSFTNGP03.phx.gbl...
>|||> now it comes to fun part: to create SSIS package I must purchase license f
or Visual Studio?
No, the Visual Studio IDE is shipped with SQL Server. It is called Business
Intelligence Development
Studio.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ME" <ME@.mail.com> wrote in message news:u0yaw2wGHHA.4904@.TK2MSFTNGP04.phx.gbl...reen">
> You are correct.
> now it comes to fun part: to create SSIS package I must purchase license f
or Visual Studio?
>
> "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
> news:%23r$SwowGHHA.1264@.TK2MSFTNGP03.phx.gbl...
>