Bulk XML Upload problem
-
I have been experimenting with the BulkXMLUpload object and have had some success and a whole lot of hair pulling.
The example I have assumes that you have a 3 tier hierarchy in the file.
...
...... The problem I have is that the files that I receive have 4 tiers so effectively
...
......
......... I keep getting a message back saying that I need a relation defining in my XDR schema for my
but I don't know enough about XML to do this. Can anyone help?
-
Do you want to post a sample data file and the database schema you are trying to import into and I'll try and put something together.
-
Thanks, I should be extremely grateful for any help you can offer.
Well the schema I came up with looks like this
<?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="BetID" dt:type="string" />
<ElementType name="CustomerID" dt:type="string" />
<AttributeType name="evid" dt:type="int" />
<AttributeType name="CourseName" dt:type="string" />
<AttributeType name="DateTime" dt:type="date" />
<ElementType name="root" sql:is-constant="1">
<element type="BetDetails" />
</ElementType>
<ElementType name="SelectionDetails">
<attribute type="evid" sql:field="evid" />
<attribute type="CourseName" sql:field="CourseName" />
<attribute type="DateTime" sql:field="EventTime" />
</ElementType>
<ElementType name="Selections" sql:is-constant="1">
<element type="SelectionDetails /">
</ElementType
<ElementType name="BetDetails" sql:relation="BetDetails">
<element type="BetID" sql:field="BetID" />
<element type="CustomerID" sql:field="CustomerID" />
<element type="Selections" >
<sql:relationship
key-relation="BetDetails"
key="BetID"
foreign-key="BetID"
foreign-relation="Selections" />
</element>
</ElementType>
</Schema>
And the sample data looks like
<Message xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" id="0000000001">
<Bets>
<BetDetails>
<BetID>199FC240304E2000</BetID>
<CustomerID>D473E82366515B1BF283DC33B05323A9</CustomerID>
<CustomerID_RP></CustomerID_RP>
<Selections>
<SelectionDetails evid='0' CourseName='NEWMARKET' DateTime='2005-09-30 14:05'></SelectionDetails>
<SelectionDetails evid='0' CourseName='HAYMARKET' DateTime='2005-09-30 15:00'></SelectionDetails>
</Selections>
</BetDetails>
</Bets>
</Message>
-
Here is the script to create the test tables I used
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Selection_Bet]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Selection] DROP CONSTRAINT FK_Selection_Bet
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Bet]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Bet]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Selection]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Selection]
GO
CREATE TABLE [dbo].[Bet] (
[BetID] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CustomerID] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CustomerID_RP] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Selection] (
[SelectionID] [int] IDENTITY (1, 1) NOT NULL ,
[BetID] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[EvID] [int] NOT NULL ,
[CourseName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SelectionDateTime] [smalldatetime] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Bet] WITH NOCHECK ADD
CONSTRAINT [PK_Bet] PRIMARY KEY CLUSTERED
(
[BetID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Selection] WITH NOCHECK ADD
CONSTRAINT [PK_Selection] PRIMARY KEY CLUSTERED
(
[SelectionID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Selection] ADD
CONSTRAINT [FK_Selection_Bet] FOREIGN KEY
(
[BetID]
) REFERENCES [dbo].[Bet] (
[BetID]
)
GO
And here is the schema I used to bulk load
And finally (although you might not need it) here is the vbscript (windows scripting host file)
Option Explicit
Const FILEPATH_TRANSFORMDIR = "C:\Temp\"
Const DB_CONN_STRING = "provider=SQLOLEDB;data source=SERVERNAME;database=DATABASENAME;integrated security=SSPI"
DimFILEPATH_ERRORLOG
DimFILEPATH_SCHEMA
Dim FILEPATH_DATA
FILEPATH_ERRORLOG = FILEPATH_TRANSFORMDIR & "sqlxmlerror.xml"
FILEPATH_SCHEMA = FILEPATH_TRANSFORMDIR & "test.xsd"
FILEPATH_DATA = FILEPATH_TRANSFORMDIR & "test.xml"
Dim xmlDoc
Dim objBL
Dim oFSO
Dim oTS
Dim objSQLXMLBL ' Bulk Load Object.
Set oFSO = CreateObject("Scripting.FileSystemObject")
If oFSO.FileExists(FILEPATH_ERRORLOG) Then
oFSO.DeleteFile FILEPATH_ERRORLOG
End If
Set xmlDoc = LoadXmlDocument(FILEPATH_DATA)
Set objSQLXMLBL = CreateObject("SQLXMLBULKLOAD.SQLXMLBulkLoad.3.0")
WScript.Echo("Creating SQLXML Bulk Load object and connecting to database...")
With objSQLXMLBL
.ConnectionString = DB_CONN_STRING
.Transaction = False
.TempFilePath = Left(FILEPATH_TRANSFORMDIR, Len(FILEPATH_TRANSFORMDIR)-1)
.BulkLoad = True
.ErrorLogFile = FILEPATH_ERRORLOG
.ForceTableLock = True
.CheckConstraints = True
.KeepIdentity = False
.KeepNulls = True
End With
On Error Resume Next
WScript.Echo("Bulk loading '" & FILEPATH_DATA & "' using schema '" & FILEPATH_SCHEMA & "'...")
objSQLXMLBL.Execute FILEPATH_SCHEMA, FILEPATH_DATA
' Commit only if there are no errors. Otherwise rollback.
If Err.Number = 0 Then
WScript.Echo("No errors returned")
Else
WScript.Echo(Err.Description)
End If
Set objSQLXMLBL = Nothing
On Error GoTo 0
If oFSO.FileExists(FILEPATH_ERRORLOG) Then
Set oTS = oFSO.OpenTextFile(FILEPATH_ERRORLOG, 1, False, -1)
WScript.Echo("Error Log Contents" & vbCrLf & vbCrLf & oTS.ReadAll)
oTS.Close
Else
WScript.Echo("No errors")
End If
Set oTS = Nothing
Set oFSO = Nothing
WScript.Echo(vbCrLf & "Hit Enter to quit...")
WScript.StdIn.ReadLine()
Private Function LoadXmlDocument(filePath)
Dim xmlDoc ' MSXML.DOMDocument
WScript.Echo("Loading xml document '" & filePath & "'...")
If Not oFSO.FileExists(filePath) Then
WScript.Echo("Failed to locate required file '" & filePath & "'")
Exit Function
End If
Set xmlDoc = CreateObject("MSXML2.DOMDocument.4.0")
With xmlDoc
.ValidateOnParse = True
.Async = False
.Load filePath
End With
Set LoadXmlDocument = xmlDoc
End Function
Hope this helps
-
Oops, schema failed to paste. I'll try again. Cool project BTW. Importing bets on the GGs lol
-
Can't get the schema to paste. Must be doin something wrong. Try again......
<xs:schema xmlns:sql="urn:schemas-microsoft-com:mapping-schema" xmlns:xs="http://www.w3.org/2001/XMLSchema" elementformdefault="qualified">
<xs:annotation>
<xs:appinfo>
<sql:relationship name="BetDetails" parent="Bet" parent-key="BetID" child="Selection" child-key="BetID"/>
</xs:appinfo>
</xs:annotation>
<xs:element name="Message" sql:is-constant="1">
<xs:complextype>
<xs:sequence>
<xs:element name="Bets" sql:is-constant="1">
<xs:complextype>
<xs:sequence>
<xs:element name="BetDetails" type="BetDetailsType" minoccurs="1" maxoccurs="unbounded" sql:relation="Bet"/>
</xs:sequence>
</xs:complextype>
</xs:element>
</xs:sequence>
<xs:attribute name="id" type="xs:positiveInteger" sql:mapped="false"/>
</xs:complextype>
</xs:element>
<!-- Bet Details Type -->
<xs:complextype name="BetDetailsType">
<xs:sequence>
<xs:element name="BetID" type="xs:string" sql:field="BetID"/>
<xs:element name="CustomerID" type="xs:string" sql:field="CustomerID"/>
<xs:element name="CustomerID_RP" type="xs:string" sql:field="CustomerID_RP"/>
<xs:element name="Selections" sql:is-constant="1">
<xs:complextype>
<xs:sequence>
<xs:element name="SelectionDetails" type="SelectionDetailsType" minoccurs="1" maxoccurs="unbounded" sql:relation="Selection" sql:relationship="BetDetails"/>
</xs:sequence>
</xs:complextype>
</xs:element>
</xs:sequence>
</xs:complextype>
<!-- Selection Details Type -->
<xs:complextype name="SelectionDetailsType">
<xs:attribute name="evid" type="xs:integer" sql:field="EvID"/>
<xs:attribute name="CourseName" type="xs:string" sql:field="CourseName"/>
<xs:attribute name="DateTime" type="xs:string" sql:field="SelectionDateTime"/>
</xs:complextype>
</xs:schema>
-
WOW!!!!!!!
I would never have believed this could be done so quickly!
Can I ask how you generated the Schema? Is it via a tool or is it manually via an indepth knowledge of XML?
This could provide the basis of a decent article if you were up for it
As you noticed this forum does not like tags but I got your schema using view-source and replacing the <br%gt; with carriage returns.
-
You can use various tools to generate schemas from sample data files (e.g. XMLSpy) but you would still have to add the sql mapping stuff yourself. The generated schemas can be a bit messy however, so I always end up writing them manually (yes, I've worked with xml for a while now).
Thinking about it, it wouldn't be that difficult to write a simple tool that you could use to visually map between xml elements/attributes and sql tables. I might start one and stick it on sourceforge. Obviously if you/your company has got the cash then the same can be achieved by bringing in the big guns and using BizTalk (v expensive though).
Anyway, thanks for the response, glad it helped you get done what you needed. I'm not sure I've got the necessary writing skills to turn this into an article but I'd be happy to post further info if you start having difficulties.
-
I was attempting to follow this example since everything was nicely provided. This is my first attempt at import xml into SQLServer. I got this error:
Schema: unable to load schema 'test.xsd'. An error occurred (test.xsd#/schema[1]
The attribute "elementformdefault" is not supported on the declaration xs:schema.).
So, I just removed that attribute.
Now, I'm getting this error:
Schema: unable to load schema 'test.xsd'. An error occurred (test.xsd#/schema[1]/element[position() = 1 and @name = 'Message']/complextype[1]
Element "xs:complextype" is not allowed in this context.).Could anyone point me in the right direction?TIA for your help!
-
After playing with the xsd file this is what I got to work...seems contextType is case sensitive (vs contexttype)...
<?xml version="1.0" encoding="ISO-8859-1"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema"
elementFormDefault="qualified">
<xs:annotation>
<xs:appinfo>
<sql:relationship name="BetDetails" parent="Bet" parent-key="BetID" child="Selection" child-key="BetID"/>
</xs:appinfo>
</xs:annotation>
<xs:element name="Message" sql:is-constant="1">
<xs:complexType>
<xs:sequence>
<xs:element name="Bets" sql:is-constant="1">
<xs:complexType>
<xs:sequence>
<xs:element name="BetDetails" type="BetDetailsType" sql:relation="Bet"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute name="id" type="xs:positiveInteger" sql:mapped="false"/>
</xs:complexType>
</xs:element>
<!-- Bet Details Type -->
<xs:complexType name="BetDetailsType">
<xs:sequence>
<xs:element name="BetID" type="xs:string" sql:field="BetID"/>
<xs:element name="CustomerID" type="xs:string" sql:field="CustomerID"/>
<xs:element name="CustomerID_RP" type="xs:string" sql:field="CustomerID_RP"/>
<xs:element name="Selections" sql:is-constant="1">
<xs:complexType>
<xs:sequence>
<xs:element name="SelectionDetails" type="SelectionDetailsType" sql:relation="Selection" sql:relationship="BetDetails"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
<!-- Selection Details Type -->
<xs:complexType name="SelectionDetailsType">
<xs:attribute name="evid" type="xs:integer" sql:field="EvID"/>
<xs:attribute name="CourseName" type="xs:string" sql:field="CourseName"/>
<xs:attribute name="DateTime" type="xs:string" sql:field="SelectionDateTime"/>
</xs:complexType>
</xs:schema>
-
Yes, the XML is defined to be case sensitive. Its bitten me too.
For a universal data transfer language, it seems a bit silly to have case sensitivity a requirement. -
Apart from VB and SQL are there any other current computer languages that are not case sensitive by default?
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply