XMLBulkLoad - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

XMLBulkLoad

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-07-23 : 07:29:27

Sean writes "I'm trying to import data into a SQL Server table from an XML file with the following structure:

<Root>
<Header>
<Sequence>1</Sequence>
</Header>
<Detail>
<Name>Sean</Name>
</Detail>
<Detail>
<Name>Samantha</Name>
</Detail>
</Root>

That's the general idea. What i'd like to do though is have the Sequence from the header written into each record as follows:

Sequence Name
1 Sean
1 Samantha

I'm tied to use the XML structure above, as the data is coming from an external source. I'm using XMLBulkLoad, as potentially there could be up to 1/2 million records per insert. I need some help with the schema file, as the only info that i have found requires that the Sequence be in each detail element.

Thanks

Sean"

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-07-24 : 13:44:56

If your xml is structured exactly as your sample piece
(i.e. multilined and only one kind of tags per each line)
then you may convert the *.xml into the table-like plain
text file by this my VBscript code... then just use bcp.

Dim fso, tso, curr, s, i, j
Set fso = CreateObject("Scripting.FileSystemObject")
Set tso = fso.OpenTextFile("d:\my.xml", 1)

While Not tso.AtEndOfStream
curr = tso.ReadLine
If Left(curr, 10) = "<Sequence>" Then
i = Replace(Replace(curr, "<Sequence>", ""), "</Sequence>", "")
ElseIf Left(curr, 6) = "<Name>" Then
j = Replace(Replace(curr, "<Name>", ""), "</Name>", "")
s = s & CStr(i) & Chr(9) & j & Chr(13) & Chr(10)
End If
Wend

s = "Sequence" & Chr(9) & "Name" & Chr(13) & Chr(10) & s
tso.Close
Set tso = fso.CreateTextFile("d:\xml.txt", True, False)
tso.Write s
tso.Close
Set tso = Nothing
Set fso = Nothing

- Vit

Sean Fox
Starting Member

5 Posts

Posted - 2003-07-29 : 04:32:57

Thanks for the reply. Although VBScript will achieve what i'm attempting to do it's not the ideal. We currently already do a tranformation to tab-delimited format using a Delphi application, that is also used for the file transfer process. This is a bit of a botteleneck at the moment. If we just copy the code into VBScript then we are simple transferring the problem.

I know that currently we can bulkload a 450mb document containing 250,000 rows in two to three minutes. I want to be able to maintain that sort of load time, whilst removing the transformation from the application. What i'm really looking for is some way of doing this within the mapping schema.

Cheers

Sean

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-07-29 : 06:01:10

This is my wild gibberish... Maybe you'll manage to get some useful hint from it.

<?xml version="1.0"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:ms="urn:schemas-microsoft-com:mapping-schema">

<xs:annotation><xs:appinfo>
<ms:relationship name="myTable" />
</xs:appinfo></xs:annotation>

<xs:element name="Root" ms:relation="myTable">
<xs:complexType>
<xs:sequence>
<xs:element name="Sequence" ms:field="Sequence" ms:datatype="int" />

<xs:complexType>
<xs:sequence>
<xs:element name="Name" minOccurs="0"
maxOccurs="unbounded" ms:relation="myTable"
ms:field="Name" ms:datatype="nvarchar(50)" />
</xs:sequence>
</xs:complexType>

</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>

</xs:schema>

- Vit

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-07-29 : 07:16:46

Have you tried using XSLT to transform your XML into CSV format? It may be faster, it certainly will require less programming. Once it's in CSV format you can use BULK INSERT or bcp to import it into SQL Server (and 250,000 rows could take less than 30 seconds to import)

Sean Fox
Starting Member

5 Posts

Posted - 2003-07-29 : 07:28:04

Had a look at that a couple of weeks ago, but as it's a 450mb document, i'm limited to XSLT with SAX. I'm sure this is also possible, but don't know too much about how to go about it.

Maybe more research is necessary?

Cheers.

Sean

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-07-29 : 07:36:03

http://www.xmlfiles.com/

That should get you started. You're not "limited" to SAX or XSLT, these are the perfect tools for the job. Essentially you just want to zip through the XML and strip out the tags, leaving only the data. You'd never want to load a 450 MB document into a DOM anyway.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources