Re: Importing XML into SQL using DTS

Tech-Archive recommends: Fix windows errors by optimizing your registry

Try to use attributes property on Dom nodes to get the values of attributes:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/xmlsdk/html/75e7840a-e81d-4c42-ab63-9a65757ceb24.asp

--
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.



"Fec" <Fec@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:E454BA66-8786-403D-BC80-406028CCFDAE@xxxxxxxxxxxxxxxx
>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=sa;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").nodeTypedValue
> .fields("Publisher") =
> objBookNode.selectSingleNode("Publisher").nodeTypedValue
> .fields("DateOfPurchase") =
> objBookNode.selectSingleNode("DateOfPurchase").nodeTypedValue
>
> .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


.

Relevant Pages

  • Re: Loading mulitple files
    ... Afterwards I pull all the xml files to a ... dim logXML, logPath, logfile, logtext, arrLogs, arrText ... set att = logXML.createAttribute ... Set oApps = oXMLDoc.DocumentElement.SelectNodes ...
    (microsoft.public.scripting.vbscript)
  • Re: Loading mulitple files
    ... Afterwards I pull all the xml files to a ... dim logXML, logPath, logfile, logtext, arrLogs, arrText ... : set att = logXML.createAttribute ...
    (microsoft.public.scripting.vbscript)
  • ASP.Net app cannot read XML with Anonymous Authentication disabled
    ... real simple XML files from it's local directory. ... Dim ProjReader As XmlReader = Nothing ... 'Get XML Data for Projector and load in Projector Dropdown ...
    (microsoft.public.dotnet.framework.aspnet.security)
  • Re: Importing XML into SQL using DTS
    ... >I need to import large XML files into an SQL table. ... > Dim objXMLDOM ... > Dim objADORS ...
    (microsoft.public.sqlserver.xml)
  • xml 2 sql
    ... Ok here is what I am trying to accomplish I have 12 xml files that I want to ... Dim aXMLFiles As Array ... Step 3 Connect to the sqlce database ... Dim SqlCeEngine As SqlCeEngine ...
    (microsoft.public.dotnet.framework.compactframework)