Showing posts with label import. Show all posts
Showing posts with label import. Show all posts

Monday, March 26, 2012

Optimized way of Importing more than 300000 lines of xml data into SQL

Hi,

We have a 120 MB huge relational xml file, that we parse it through xsd file and import it into SQL Server relational tables using .net Dataset class. It takes around 2 hours to validate and import this huge file into sql server.

When the import is happening most of the Server CPU and memory resources are also more than 50%.

We would like to increase the performance of this import and we are ready to redesign the application if we find a better solution.

Any ideas/suggestions sincerely appreciated.

javed.

Hi Javed,

You can use XML Bulk Load component to import your xml data directly into SQL Server.

Refer Books on line SQL Server 2005 :

SQL Server 2005 Books online ->

SQL Server Programming Reference ->

SQL XML 4.0 Programming ->

Performing Bulk Load of XML Data (SQLXML 4.0)

Refer the following links to know more about XmlBulkLoad component:

Performing Bulk Load of XML Data (SQLXML 4.0)

Using SQL Server's XML Support > XML Bulk Load

How to import XML into SQL Server with the XML Bulk Load component

Thanks

Naras.

Friday, March 23, 2012

Optimize for Many Tables and Temp files

We are using the Import/Export wizard to create some simple packages to transfer tables. When doing a lrge number of tables, the 'Optimize for many tables' option is automatically selected (as noted in BOL). What we've found is that the package creates a bunch of temp files in the creator's Documents and Settings....Temp folder. Needless to say this package cannot be re-run later, nor scheduled, since the path referenced doesn't necessarily exist on the server. Is there a way to specify where these files should be created so thatthe package is re-usable and still be optimized?

Steve

Yes, we already had people complaining about this.

There are a few options to workaround this, though:

1. Move all those files to the safe location and update the package accordingly.

2. If your number of tables is not too big (less than 100 should work on an average machine), uncheck the "Optimize for many tables" option and see if it runs for you. If it does not run, create a few sequential data flows and split the simple flows that the wizard had placed in the single Data Flow task into several of them. You can use the Copy/Paste for that.

HTH.

Monday, February 20, 2012

OPENXML question

I've got thousands of XML docs and have to import those to the DB. And
I am having problem with getting some values because of tricky XML
format.

I didn't create this XML format and I don't like this format, OOP snob
did. And I(DBA) have to look after their mess.
I've got XML doc like this :
<customer>
<customer_id>12345</customer_id>
<first_name>Jason</first_name>
<last_name>Varitek</last_name>
<location>
<city>Boston</city>
<state>MA</state>
</location>
</customer
First, I did following :

DECLARE @.idoc int
DECLARE @.doc varchar(1000)
SET @.doc ='
<customer>
<customer_id>12345</customer_id>
<first_name>Jason</first_name>
<last_name>Varitek</last_name>
<location>
<city>Boston</city>
<state>MA</state>
</location>
</customer>'
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc

SELECT *
FROM OPENXML (@.idoc, '/customer',2)
WITH (customer_id int,
first_name varchar(50),
last_name varchar(50),
location varchar(50))

Then returned this which is not bad.

customer_id | first_name |last_name |location
12345 | Jason | Varitek |Boston MA

But I need to return city and state in separated columns(I wish OOP
snobs made these as attributes in location element, instead of
elements). And I tried following and returned everything NULL.

DECLARE @.idoc int
DECLARE @.doc varchar(1000)
SET @.doc ='
<customer>
<customer_id>12345</customer_id>
<first_name>Jason</first_name>
<last_name>Varitek</last_name>
<location>
<city>Boston</city>
<state>MA</state>
</location>
</customer>'

EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc

SELECT *
FROM OPENXML (@.idoc, '/customer/customer_id',2)
WITH (customer_id int '../@.customer_id',
first_name varchar(50) '../@.first_name',
last_name varchar(50) '../@.last_name',
city varchar(50) '@.city',
state varchar(50) '@.state')

Does anybody have any idea to get value of city & state elements in
separated columns?

Thanks

JimmySELECT *
FROM OPENXML (@.idoc, '/customer',2)
WITH (customer_id int,
first_name varchar(50),
last_name varchar(50),
city varchar(50) './location/city',
state varchar(50) './location/state')|||Thanks.

OPENXML question

Hi!

I am trying to import an xml file into a SQL 2005 table using sp_xml_prepareDocument ...OPENXML.

I always get 0 rows affected even though there is data in the file. The table structure is identical to the XML output. The OpenXML qry uses the following syntax:

FROM OPENXML(@.xmlHndAdd, '/NewDataSet/Table1', 1)
WITH MyTbl

The XML file format is:

<NewDataSet xmlns="">
<Table1 diffgr:id="Table11" msdata:rowOrder="0">
<program_id>1-2-3-4-5</program_id>
<object_name />
</Table1>
<Table1 diffgr:id="Table12" msdata:rowOrder="1">
<object_id>6-7-8-9-0</object_id>
<object_name>ABC</object_name>
<objectproperty_id>1-3-5-7-9</objectproperty_id>
</Table1>

Any suggestions are greatly appreciated!!!

Thank you!

Found the right syntax if anyone has similar questions:

DECLARE @.xmlHndAdd INT

EXEC sp_xml_prepareDocument @.xmlHndAdd OUTPUT, @.availabilityXml

TRUNCATE TABLE mytbl

INSERT mytbl

SELECT *

FROM OPENXML(@.xmlHndAdd, '//NewDataSet/Table1', 2)

WITH mytbl