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
>