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 Problems with XML and XSD into SQL 2000

Im new to everything I'm tring to import an XML file using the bulkload method in an vb script within a DTS package. It initally gave me errors of "relationship expected on 'EventList'" but after some research I specified a relationship within my xsd f
ile for events with an annotation. However, it still gives me "the parent/child table of the relationship on 'EventList' does not match." I think the reason is the parent child relationship is not necessarly linked by any subelement key. Rather the one of
the two "main" elements in this group "Event" appears in as a "sub"element or referenced element for the other "main"element "EventList." What code can i use to establish the SQL:relationship in a way that correctly describes the elements' relationship?
Here's part of the xsd, a little long I apologize:
<xs:annotation><xs:appinfo><sql:relationship name="Events" parent="Event" parent-key="Event" child="EventList" child-key="Event"/></xs:appinfo></xs:annotation><xs:element name="Event" sql:relation="Event" sql:relationship="Events"><xs:complexType><xs:seq
uence><xs:element ref="Action"/><xs:element ref="Date"/><xs:element ref="Time"/><xs:element ref="SeqNo"/><xs:element ref="ConnID"/><xs:element ref="User"/><xs:element ref="Code"/><xs:choice><xs:element ref="UserInfo"/><xs:element ref="EntryInfo"/></xs:cho
ice></xs:sequence><xs:attribute name="ver" type="xs:string"/></xs:complexType></xs:element><xs:element name="EventList" sql:relation="EventList" sql:relationship="Events"><xs:complexType><xs:sequ ence><xs:element ref="Event" minOccurs="0" maxOccurs="unboun
ded"/></xs:sequence></xs:complexType></xs:element>
thanks kind souls
Can you post a sample of the XML you're trying to import as well as the
table defs you're importing into?
Graeme Malcolm
Principal Technologist
Content Master Ltd.
"Mardi Morillo" <mmorillo@.mbcsystems.org> wrote in message
news:4B3F602E-394B-46F7-B711-FDDC69558389@.microsoft.com...
> Im new to everything I'm tring to import an XML file using the bulkload
method in an vb script within a DTS package. It initally gave me errors of
"relationship expected on 'EventList'" but after some research I specified a
relationship within my xsd file for events with an annotation. However, it
still gives me "the parent/child table of the relationship on 'EventList'
does not match." I think the reason is the parent child relationship is not
necessarly linked by any subelement key. Rather the one of the two "main"
elements in this group "Event" appears in as a "sub"element or referenced
element for the other "main"element "EventList." What code can i use to
establish the SQL:relationship in a way that correctly describes the
elements' relationship? Here's part of the xsd, a little long I apologize:
> <xs:annotation><xs:appinfo><sql:relationship name="Events" parent="Event"
parent-key="Event" child="EventList"
child-key="Event"/></xs:appinfo></xs:annotation><xs:element name="Event"
sql:relation="Event"
sql:relationship="Events"><xs:complexType><xs:sequ ence><xs:element
ref="Action"/><xs:element ref="Date"/><xs:element ref="Time"/><xs:element
ref="SeqNo"/><xs:element ref="ConnID"/><xs:element ref="User"/><xs:element
ref="Code"/><xs:choice><xs:element ref="UserInfo"/><xs:element
ref="EntryInfo"/></xs:choice></xs:sequence><xs:attribute name="ver"
type="xs:string"/></xs:complexType></xs:element><xs:element name="EventList"
sql:relation="EventList"
sql:relationship="Events"><xs:complexType><xs:sequ ence><xs:element
ref="Event" minOccurs="0"
maxOccurs="unbounded"/></xs:sequence></xs:complexType></xs:element>
> thanks kind souls
>
|||Heres some of the xml file. By the way i converted the dtd to an xsd that i posted last time:
<?xml version="1.0" encoding="ISO-8859-1"?><!DOCTYPE LaserFicheAuditTrail SYSTEM "LFAudit.dtd"><LaserFicheAuditTrail Version="2.0"><Database>MBC</Database><EventList><Event><Action>LOGIN</Action><Date>28-Feb-2004</Date><Time>22:35:47</Time><SeqNo>1</SeqNo
><ConnID>1</ConnID><User>ADMIN</User><Code>7331</Code><UserInfo><ConnType>RW</ConnType></UserInfo></Event><Event><Action>LOGIN</Action><Date>28-Feb-2004</Date><Time>22:35:54</Time><SeqNo>2</SeqNo><ConnID>2</ConnID><User>ADMIN</User><Code>0</Code><UserInfo
><ConnType>RW</ConnType></UserInfo></Event><Event><Action>LOGOUT</Action><Date>28-Feb-2004</Date><Time>22:36:17</Time><SeqNo>3</SeqNo><ConnID>2</ConnID><User>ADMIN</User><Code>0</Code><UserInfo><ConnType>RW</ConnType></UserInfo></Event><Event><Action>LOGI
N</Action><Date>28-Feb-2004</Date><Time>22:37:05</Time><SeqNo>4</SeqNo><ConnID>3</ConnID><User>ADMIN</User><Code>7331</Code><UserInfo><ConnType>RW</ConnType></UserInfo></Event><Event><Action>LOGIN</Action><Date>28-Feb-2004</Date><Time>22:37:11</Time><SeqN
o>5</SeqNo><ConnID>4</ConnID><User>ADMIN</User><Code>0</Code><UserInfo><ConnType>RW</ConnType></UserInfo></Event><Event><Action>VIEW</Action><Date>28-Feb-2004</Date><Time>22:37:14</Time><SeqNo>6</SeqNo><ConnID>4</ConnID><User>ADMIN</User><Code>0</Code><En
tryInfo><EntryName>(1) SCANNED DOCUMENTS</EntryName><EntryID>23</EntryID><ParentID>1</ParentID><EntryType>FOLDER</EntryType></EntryInfo></Event><Event><Action>VIEW</Action><Date>28-Feb-2004</Date><Time>22:46:42</Time><SeqNo>7</SeqNo><ConnID>4</ConnID><Use
r>ADMIN</User><Code>0</Code><EntryInfo><EntryName>(1) SCANNED DOCUMENTS</EntryName><EntryID>23</EntryID><ParentID>1</ParentID><EntryType>FOLDER</EntryType></EntryInfo></Event>
|||And the database tables?
Graeme Malcolm
Principal Technologist
Content Master Ltd.
"Mardi Morillo" <mmorillo@.mbcsystems.org> wrote in message
news:1C9D3C7F-C25E-46FC-A35E-2845454D5B5B@.microsoft.com...
> Heres some of the xml file. By the way i converted the dtd to an xsd that
i posted last time:
> <?xml version="1.0" encoding="ISO-8859-1"?><!DOCTYPE LaserFicheAuditTrail
SYSTEM "LFAudit.dtd"><LaserFicheAuditTrail
Version="2.0"><Database>MBC</Database><EventList><Event><Action>LOGIN</Actio
n><Date>28-Feb-2004</Date><Time>22:35:47</Time><SeqNo>1</SeqNo><ConnID>1</Co
nnID><User>ADMIN</User><Code>7331</Code><UserInfo><ConnType>RW</ConnType></U
serInfo></Event><Event><Action>LOGIN</Action><Date>28-Feb-2004</Date><Time>2
2:35:54</Time><SeqNo>2</SeqNo><ConnID>2</ConnID><User>ADMIN</User><Code>0</C
ode><UserInfo><ConnType>RW</ConnType></UserInfo></Event><Event><Action>LOGOU
T</Action><Date>28-Feb-2004</Date><Time>22:36:17</Time><SeqNo>3</SeqNo><Conn
ID>2</ConnID><User>ADMIN</User><Code>0</Code><UserInfo><ConnType>RW</ConnTyp
e></UserInfo></Event><Event><Action>LOGIN</Action><Date>28-Feb-2004</Date><T
ime>22:37:05</Time><SeqNo>4</SeqNo><ConnID>3</ConnID><User>ADMIN</User><Code
>7331</Code><UserInfo><ConnType>RW</ConnType></UserInfo></Event><Event><Acti
on>LOGIN</Action><Date>28-Feb-2004</Date><Time>22:37:11</Time><SeqNo>5</SeqN
o><ConnID>4</ConnID><User>ADMIN</User><Code>0</Code><UserInfo><ConnType>RW</
ConnType></UserInfo></Event><Event><Action>VIEW</Action><Date>28-Feb-2004</D
ate><Time>22:37:14</Time><SeqNo>6</SeqNo><ConnID>4</ConnID><User>ADMIN</User
><Code>0</Code><EntryInfo><EntryName>(1) SCANNED
DOCUMENTS</EntryName><EntryID>23</EntryID><ParentID>1</ParentID><EntryType>F
OLDER</EntryType></EntryInfo></Event><Event><Action>VIEW</Action><Date>28-Fe
b-2004</Date><Time>22:46:42</Time><SeqNo>7</SeqNo><ConnID>4</ConnID><User>AD
MIN</User><Code>0</Code><EntryInfo><EntryName>(1) SCANNED
DOCUMENTS</EntryName><EntryID>23</EntryID><ParentID>1</ParentID><EntryType>F
OLDER</EntryType></EntryInfo></Event>
|||CREATE TABLE AUDIT_XML_IMPORT
([Action]VARCHAR(100) NULL,
[AnnotateInfo]VARCHAR(100) NULL,
[AnnotColor]VARCHAR(100) NULL,
[AnnotHeight]VARCHAR(100) NULL,
[AnnotID]VARCHAR(100) NULL,
[AnnotLinked]VARCHAR(100) NULL,
[AnnotStampID]VARCHAR(100) NULL,
[AnnotStampName]VARCHAR(100) NULL,
[AnnotType]VARCHAR(100) NULL,
[AnnotWidth]VARCHAR(100) NULL,
[AnnotX]VARCHAR(100) NULL,
[AnnotY]VARCHAR(100) NULL,
[BackColor]VARCHAR(100) NULL,
[Code]VARCHAR(100) NULL,
[ConnID]VARCHAR(100) NULL,
[ConnType]VARCHAR(100) NULL,
[CopyInfo]VARCHAR(100) NULL,
[CreateInfo]VARCHAR(100) NULL,
[Database]VARCHAR(100) NULL,
[Date]VARCHAR(100) NULL,
[DestVolumeID]VARCHAR(100) NULL,
[DestVolumeName]VARCHAR(100) NULL,
[EntryID]VARCHAR(100) NULL,
[EntryInfo]VARCHAR(100) NULL,
[EntryName]VARCHAR(100) NULL,
[EntryType]VARCHAR(100) NULL,
[Event]VARCHAR(100) NULL,
[EventList]VARCHAR(100) NULL,
[ExportComment]VARCHAR(100) NULL,
[ExportInfo]VARCHAR(100) NULL,
[ExportMethod]VARCHAR(100) NULL,
[ExportPages]VARCHAR(100) NULL,
[ExportReason]VARCHAR(100) NULL,
[FieldAfter]VARCHAR(100) NULL,
[FieldBefore]VARCHAR(100) NULL,
[FieldChange]VARCHAR(100) NULL,
[FieldList]VARCHAR(100) NULL,
[FieldName]VARCHAR(100) NULL,
[FieldsInfo]VARCHAR(100) NULL,
[FieldType]VARCHAR(100) NULL,
[FuzzyParameter]VARCHAR(100) NULL,
[HiliteInfo]VARCHAR(100) NULL,
[HiliteTextInfo]VARCHAR(100) NULL,
[LaserFicheAuditTrail]VARCHAR(100) NULL,
[MigrateInfo]VARCHAR(100) NULL,
[ModifyType]VARCHAR(100) NULL,
[MoveInfo]VARCHAR(100) NULL,
[NewName]VARCHAR(100) NULL,
[NewParentID]VARCHAR(100) NULL,
[NewRefID]VARCHAR(100) NULL,
[NoteInfo]VARCHAR(100) NULL,
[NoteText]VARCHAR(100) NULL,
[NumPages]VARCHAR(100) NULL,
[Page]VARCHAR(100) NULL,
[ParentID]VARCHAR(100) NULL,
[RedactColor]VARCHAR(100) NULL,
[RedactInfo]VARCHAR(100) NULL,
[RedactTextInfo]VARCHAR(100) NULL,
[RenameInfo]VARCHAR(100) NULL,
[SearchInfo]VARCHAR(100) NULL,
[SearchString]VARCHAR(100) NULL,
[SeqNo]VARCHAR(100) NULL,
[SourceID]VARCHAR(100) NULL,
[SourceVolumeID]VARCHAR(100) NULL,
[SourceVolumeName]VARCHAR(100) NULL,
[StampInfo]VARCHAR(100) NULL,
[StampMasterInfo]VARCHAR(100) NULL,
[StampPersonalInfo]VARCHAR(100) NULL,
[StartPage]VARCHAR(100) NULL,
[TemplateID]VARCHAR(100) NULL,
[TemplateName]VARCHAR(100) NULL,
[TextColor]VARCHAR(100) NULL,
[TextEnd]VARCHAR(100) NULL,
[TextStart]VARCHAR(100) NULL,
[Time]VARCHAR(100) NULL,
[User]VARCHAR(100) NULL,
[UserInfo]VARCHAR(100) NULL,
[Version]VARCHAR(100) NULL,
[VolumeID]VARCHAR(100) NULL,
[VolumeName]VARCHAR(100) NULL,)
GO
|||The problem is that you've only got a single table - the relationship you've
declared in the schema is trying to join a table called Event to a table
called EventList - neither of which exist in your database.
I'd suggest that the easiest approach would be to "flatten" the data using
an XSLT before performing the bulk load. See
http://sqlxml.org/faqs.aspx?faq=49.
Graeme Malcolm
Principal Technologist
Content Master Ltd.
"Mardi" <mmorillo@.mbcsystems.org> wrote in message
news:ECC157F3-BD9E-4415-A248-8D85E6EE7A7B@.microsoft.com...
> CREATE TABLE AUDIT_XML_IMPORT
> ([Action]VARCHAR(100) NULL,
> [AnnotateInfo]VARCHAR(100) NULL,
> [AnnotColor]VARCHAR(100) NULL,
> [AnnotHeight]VARCHAR(100) NULL,
> [AnnotID]VARCHAR(100) NULL,
> [AnnotLinked]VARCHAR(100) NULL,
> [AnnotStampID]VARCHAR(100) NULL,
> [AnnotStampName]VARCHAR(100) NULL,
> [AnnotType]VARCHAR(100) NULL,
> [AnnotWidth]VARCHAR(100) NULL,
> [AnnotX]VARCHAR(100) NULL,
> [AnnotY]VARCHAR(100) NULL,
> [BackColor]VARCHAR(100) NULL,
> [Code]VARCHAR(100) NULL,
> [ConnID]VARCHAR(100) NULL,
> [ConnType]VARCHAR(100) NULL,
> [CopyInfo]VARCHAR(100) NULL,
> [CreateInfo]VARCHAR(100) NULL,
> [Database]VARCHAR(100) NULL,
> [Date]VARCHAR(100) NULL,
> [DestVolumeID]VARCHAR(100) NULL,
> [DestVolumeName]VARCHAR(100) NULL,
> [EntryID]VARCHAR(100) NULL,
> [EntryInfo]VARCHAR(100) NULL,
> [EntryName]VARCHAR(100) NULL,
> [EntryType]VARCHAR(100) NULL,
> [Event]VARCHAR(100) NULL,
> [EventList]VARCHAR(100) NULL,
> [ExportComment]VARCHAR(100) NULL,
> [ExportInfo]VARCHAR(100) NULL,
> [ExportMethod]VARCHAR(100) NULL,
> [ExportPages]VARCHAR(100) NULL,
> [ExportReason]VARCHAR(100) NULL,
> [FieldAfter]VARCHAR(100) NULL,
> [FieldBefore]VARCHAR(100) NULL,
> [FieldChange]VARCHAR(100) NULL,
> [FieldList]VARCHAR(100) NULL,
> [FieldName]VARCHAR(100) NULL,
> [FieldsInfo]VARCHAR(100) NULL,
> [FieldType]VARCHAR(100) NULL,
> [FuzzyParameter]VARCHAR(100) NULL,
> [HiliteInfo]VARCHAR(100) NULL,
> [HiliteTextInfo]VARCHAR(100) NULL,
> [LaserFicheAuditTrail]VARCHAR(100) NULL,
> [MigrateInfo]VARCHAR(100) NULL,
> [ModifyType]VARCHAR(100) NULL,
> [MoveInfo]VARCHAR(100) NULL,
> [NewName]VARCHAR(100) NULL,
> [NewParentID]VARCHAR(100) NULL,
> [NewRefID]VARCHAR(100) NULL,
> [NoteInfo]VARCHAR(100) NULL,
> [NoteText]VARCHAR(100) NULL,
> [NumPages]VARCHAR(100) NULL,
> [Page]VARCHAR(100) NULL,
> [ParentID]VARCHAR(100) NULL,
> [RedactColor]VARCHAR(100) NULL,
> [RedactInfo]VARCHAR(100) NULL,
> [RedactTextInfo]VARCHAR(100) NULL,
> [RenameInfo]VARCHAR(100) NULL,
> [SearchInfo]VARCHAR(100) NULL,
> [SearchString]VARCHAR(100) NULL,
> [SeqNo]VARCHAR(100) NULL,
> [SourceID]VARCHAR(100) NULL,
> [SourceVolumeID]VARCHAR(100) NULL,
> [SourceVolumeName]VARCHAR(100) NULL,
> [StampInfo]VARCHAR(100) NULL,
> [StampMasterInfo]VARCHAR(100) NULL,
> [StampPersonalInfo]VARCHAR(100) NULL,
> [StartPage]VARCHAR(100) NULL,
> [TemplateID]VARCHAR(100) NULL,
> [TemplateName]VARCHAR(100) NULL,
> [TextColor]VARCHAR(100) NULL,
> [TextEnd]VARCHAR(100) NULL,
> [TextStart]VARCHAR(100) NULL,
> [Time]VARCHAR(100) NULL,
> [User]VARCHAR(100) NULL,
> [UserInfo]VARCHAR(100) NULL,
> [Version]VARCHAR(100) NULL,
> [VolumeID]VARCHAR(100) NULL,
> [VolumeName]VARCHAR(100) NULL,)
> GO
>

Importing data into MS SQL 2000 from XML file

Hi Everone,

I'm writing a script to import data from an XML file, and this tutorial is
really an awesome guide :
http://www.kbalertz.com/Q316005/Imp...Component.aspx ... problem
though is here's a snippet of my XML file:

<planets>
<sun rise="6:23" set="20:33"/>
<moon rise="18:54" set="4:26"/>
<mercury rise="7:50" set="22:11"/>
<venus rise="4:24" set="17:38"/>
<mars rise="9:45" set="23:40"/>
<jupiter rise="17:23" set="4:20"/>
<saturn rise="10:09" set="23:53"/>
<uranus rise="1:38" set="13:10"/>
<neptune rise="0:23" set="11:13"/>
<pluto rise="20:38" set="7:25"/>
</planets
<moon>
<phase date="7/6/2006" text="Waxing Gibbous">9</phase>
<phase date="7/7/2006" text="Waxing Gibbous">10</phase>
<phase date="7/8/2006" text="Waxing Gibbous">11</phase>
<phase date="7/9/2006" text="Waxing Gibbous">12</phase>
<phase date="7/10/2006" text="Waning Gibbous">14</phase>
</moon
The tutorial seems to work well with entries in XML that look like this:
<nighttime>
<txtshort>A moonlit sky</txtshort>
<txtlong>A moonlit sky</txtlong>
<weathericon>33</weathericon>
<hightemperature>100</hightemperature>
<lowtemperature>74</lowtemperature>
<realfeelhigh>108</realfeelhigh>
<realfeellow>74</realfeellow>
</nighttime
But when the XML file has more info (not sure technically what it's called)
like the date and text options above in the phase tag, I'm not sure how to
import these into MS SQL. In MS Access I did do an import from this XML
file, but alas it didn't see these entries in the phase tags either.

Suggestions? I'm trying to use the XML Bulk Load component, but I'm sure
I'm missing something. Thanks for any suggestions ...

Sam

--
Sam Alexander - sam.alexander(at)sidebandbbs(dot)com
http://www.samandflip.com or telnet://sidebandbbs.com
--
"Data is not information, Information is not knowledge, Knowledge is not
understanding, Understanding is not wisdom." -- Cliff Stoll

-- Synchronet 3.13b-Linux NewsLink 1.84
--[SideBand BBS - telnet://sidebandbbs.com]--Re: Importing data into MS SQL 2000 from XML file
By: Sam Alexander to All on Fri Jun 09 2006 09:20 am

Okay I'm getting alittle further... setting my prior question to the side,
now I'm simply trying to get data to import into the database. HEre's what
I've done.

First thing was I installed SQLXML 3.0 sp3 from MS's website and created
my schema and vbs scripts. HEre's my test import:

mydata.xml

<?xml version="1.0"?>
<mydata>
<units>
<temp>F</temp>
<dist>mi</dist>
<speed>mph</speed>
<pres>in</pres>
<prec>in</prec>
</units>
</mydata
schema.xml

<?xml version="1.0" ?>
<Schema xmlns="urn:schemas-microsoft-com:xml-data"
xmlns:dt="urn:schemas-microsoft-com:xml:datatypes"
xmlns:sql="urn:schemas-microsoft-com:xml-sql"
<ElementType name="temp" dt:type="nvarchar" />
<ElementType name="dist" dt:type="nvarchar" />
<ElementType name="speed" dt:type="nvarchar" />
<ElementType name="pres" dt:type="nvarchar" />
<ElementType name="prec" dt:type="nvarchar" /
<ElementType name="mydata" sql:is-constant="1">
<element type="Units" />
</ElementType
<ElementType name="Units"sql:relation="Units">
<element type="temp"sql:field="temp" />
<element type="dist"sql:field="dist" />
<element type="speed"sql:field="speed" />
<element type="pres"sql:field="pres" />
<element type="prec"sql:field="prec" />
</ElementType
</Schema
import.vbs
Set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")
objBL.ConnectionString = "provider=SQLOLEDB.1;data
source=mydbserver;database=mydb;uid=myusername;pwd =mypassword"
objBL.ErrorLogFile = "d:\xml\error.log"
objBL.Execute "d:\xml\schema.xml", "d:\xml\mydata.xml"
Set objBL = Nothing
(I do have my server and login info above ...)

Also I do have a table called Units in the database and here's the layout
of it:
unitstempnvarchar255
unitsdistnvarchar255
unitsspeednvarchar255
unitspresnvarchar255
unitsprecnvarchar255

When I run this I get no message nor any error log. I can test it by
changing the password and I get the unable to connect error, so I know it's
connecting to the server.

ANy suggestions? I checked the MS SQL Server processes anddidn't see any
clue that the script was logging in, but since I'm getting no errors nor
any logs showing what's happening I'm at a loss.

Thanks for any ideas or starting points on where to look. I'm at a total
loss on what to check. Thanks ...

Sam

--
Sam Alexander - sam.alexander(at)sidebandbbs(dot)com
http://www.samandflip.com or telnet://sidebandbbs.com
--
"Data is not information, Information is not knowledge, Knowledge is not
understanding, Understanding is not wisdom." -- Cliff Stoll

-- Synchronet 3.13b-Linux NewsLink 1.84
--[SideBand BBS - telnet://sidebandbbs.com]--|||I've noticed you have 'Units' and 'units' - XML is often (always?)
case-sensitive so this may not work.|||To: markc600
Re: Re: Importing data into MS SQL 2000 from XML file
By: markc600 to comp.databases.ms-sqlserver on Fri Jun 09 2006 09:31 am

> From Newsgroup: comp.databases.ms-sqlserver
>
> I've noticed you have 'Units' and 'units' - XML is often (always?)
> case-sensitive so this may not work.
> -- Synchronet 3.13b-Linux NewsLink 1.84

Hi Marc,

Thanks for the reply, and I'll check this out. Since making my post
earlier I've gotten my import to work, but to a point.

My XML file starts with this:
<?xml version="1.0" ?>
<vendor_database xmlns="http://www.vendorurl.com">
-snip-

and here's how my schema.xml file starts ...

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema"
<xsd:element name="vendor_database" sql:is-constant="true" >
-snip-

If I remove the xmlns entry from the XML data file the import runs fine,
but leaving it in there the import runs but nothing is imported. How do I
work this into the schema file? This vendor file is downloaded
automatically and no way to have this entry deleted from the xml file I"m
importing into my database.

So at this point it's just about there, just need to figure how what to do
with the xmlns entry.

Thanks --

Sam

--
Sam Alexander - sam.alexander(at)sidebandbbs(dot)com
http://www.samandflip.com or telnet://sidebandbbs.com
--
"Data is not information, Information is not knowledge, Knowledge is not
understanding, Understanding is not wisdom." -- Cliff Stoll

-- Synchronet 3.13b-Linux NewsLink 1.84
--[SideBand BBS - telnet://sidebandbbs.com]--

Importing a script into SQL Server Express

I am having trouble importing a database script into SQL Server Express. The script is from the Web Applications book from the Microsoft MCSD exams. My installation of SQL Server Express appears to be fine as I can login and create/delete databases through it. When I attempt to import the database script I get a variety of errors as follows:

Msg 911, Level 16, State 1, Server ORTHANC\SQLEXPRESS, Line 1
Could not locate entry in sysdatabases for database 'Contacts'. No entry found with that name. Make sure that the name is entered correctly.
Msg 15401, Level 16, State 1, Server ORTHANC\SQLEXPRESS, Line 1
Windows NT user or group 'ORTHANC\SQLEXPRESS\ASPNET' not found. Check the name again.
Msg 15410, Level 11, State 1, Server ORTHANC\SQLEXPRESS, Procedure sp_addrolemember, Line 80
User or role 'aspnet' does not exist in this database.
Msg 15410, Level 11, State 1, Server ORTHANC\SQLEXPRESS, Procedure sp_addrolemember, Line 80
User or role 'aspnet' does not exist in this database.

I have added access permissions for the ASPNET user account on the SQL Server Express folder but that doesn't appear to have helped. I'm not sure if the error about contacts.mdf means that I should have a contacts.mdf created as a result of the script or whether it should be there to begin with.

I found this threadhttp://forums.asp.net/thread/433540.aspx but it didn't solve my problem.

Apologies for the length of the post. Any help at all is greatly appreciated!

I think we are going to neeed to see the script you are trying to run.|||

The script is the the one from the MCSD Web apps book. After installation off the CD it's available from C:\Microsoft Press\MCSDWebApps2\Databases. Leaving out the test data added at the bottom of the script it looks like this:

USE master
GO
if exists (select * from sysdatabases where name='Contacts')
drop database Contacts
go

DECLARE @.device_directory NVARCHAR(520)
SELECT @.device_directory = SUBSTRING(phyname, 1, CHARINDEX(N'master.mdf', LOWER(phyname)) - 1)
FROM master.dbo.sysdevices
WHERE (name = N'master')

EXECUTE (N'CREATE DATABASE Contacts
ON PRIMARY (NAME = N''Contacts'', FILENAME = N''' + @.device_directory + N'contacts.mdf'')
LOG ON (NAME = N''Contacts_log'', FILENAME = N''' + @.device_directory + N'contacts.ldf'')')
go

exec sp_dboption'Contacts','trunc. log on chkpt.','true'
exec sp_dboption'Contacts','select into/bulkcopy','true'
go

set quoted_identifier on
GO
/* Set DATEFORMAT so that the date strings are interpreted correctly regardless of
the default DATEFORMAT on the server.
*/
SET DATEFORMAT mdy
GO
use"Contacts"

GO
/****** Object: Table [dbo].[Calls] Script Date: 3/7/2002 1:52:49 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Calls]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Calls]
GO

/****** Object: Table [dbo].[Contact Types] Script Date: 3/7/2002 1:52:49 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Contact Types]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Contact Types]
GO

/****** Object: Table [dbo].[Contacts] Script Date: 3/7/2002 1:52:49 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Contacts]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Contacts]

GO

/****** Object: Table [dbo].[Calls] Script Date: 3/7/2002 1:52:51 PM ******/
CREATE TABLE [dbo].[Calls] (
[CallID] [int] NOT NULL ,
[ContactID] [int] NOT NULL ,
[CallDate] [datetime] NULL ,
[CallTime] [datetime] NULL ,
[Subject] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Notes] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

GRANT REFERENCES , SELECT , INSERT , DELETE , UPDATE ON"Calls" TO"public"
GO

/****** Object: Table [dbo].[Contact Types] Script Date: 3/7/2002 1:52:55 PM ******/
CREATE TABLE [dbo].[Contact Types] (
[ContactTypeID] [int] NOT NULL ,
[ContactType] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

GRANT REFERENCES , SELECT , INSERT , DELETE , UPDATE ON"Contact Types" TO"public"
GO

/****** Object: Table [dbo].[Contacts] Script Date: 3/7/2002 1:52:55 PM ******/
CREATE TABLE [dbo].[Contacts] (
[ContactID] [int] NOT NULL ,
[FirstName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Dear] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Address] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[City] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[StateOrProvince] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PostalCode] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Region] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Country] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CompanyName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Title] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WorkPhone] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WorkExtension] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HomePhone] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MobilePhone] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FaxNumber] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EmailName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Birthdate] [smalldatetime] NULL ,
[LastMeetingDate] [smalldatetime] NULL ,
[ContactTypeID] [int] NULL ,
[ReferredBy] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Notes] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ContactsInterests] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

GRANT REFERENCES , SELECT , INSERT , DELETE , UPDATE ON"Contacts" TO"public"
GO

DECLARE @.srv varchar(100)
SET @.srv = @.@.SERVERNAME +'\ASPNET'

if not exists (select * from dbo.sysusers where name = N'aspnet' and uid < 16382)
EXEC sp_grantdbaccess @.srv, N'aspnet'
GO

if not exists (select * from dbo.sysusers where name = N'guest' and uid < 16382 and hasdbaccess = 1)
EXEC sp_grantdbaccess N'guest'
GO

exec sp_addrolemember N'db_datareader', N'aspnet'
GO

exec sp_addrolemember N'db_datawriter', N'aspnet'
GO

ALTER TABLE [dbo].[Calls] WITH NOCHECK ADD
CONSTRAINT [PK__Calls__59063A47] PRIMARY KEY CLUSTERED
(
[CallID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Contact Types] WITH NOCHECK ADD
CONSTRAINT [PK__Contact Types__5AEE82B9] PRIMARY KEY CLUSTERED
(
[ContactTypeID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Contacts] WITH NOCHECK ADD
CONSTRAINT [PK__Contacts__5CD6CB2B] PRIMARY KEY CLUSTERED
(
[ContactID]
) ON [PRIMARY]
GO

|||Since that script is from 2002, I am guessing that it's targeted for SQL Server 2000. It runs fine for me in Query Analyzer for my local SQL Server 2000.

How are you going about running it for your SQL Express database?|||

There's a batch file in the folder that runs the following command:

osql -i InstContacts.Sql -E

That produces the same errors if I use the more up to date sqlcmd with this command line:

sqlcmd -S orthanc\sqlexpress -i c:\contacts.sql -o result.txt

The first error refers to "contacts.mdf" and suggests that the name is incorrect. Should I have a contacts.mdf file or is it created by the script when it executes correctly?