| Author |
Import XML in SQL Server 2000
|
|
| George Ivanov 2005-10-27, 9:26 am |
| How to import XML file in SQL, without knowing the structure of XML
data. I tried the OPENXML function but the XML contains more than 150
tables and I can't write the schema of all of them. Is there any other
way creating database structure from XML file and inserting its data?
thanks
*** Sent via Developersdex http://www.droptable.com ***
| |
| Sreejith G 2005-10-27, 9:26 am |
| => 3 approaches.
Use the Bulk Load COM interface in SQL Server 2000 Web Release (SQLXML) 3.0
Service Pack 1 (SP1). This method of importing XML is fast, and the download
documents the Bulk Load tool with code samples.
If you don't want to extract the document's entities and attributes into
relational tables, you can use the textcopy.exe command-line utility.
If your XML document is simple, you can use Data Transformation Services
(DTS) by writing transformation code(Activex Script) that looks at each line
of the XML document and extracts the information into tables.
Thanks,
Sreejith G
"George Ivanov" wrote:
> How to import XML file in SQL, without knowing the structure of XML
> data. I tried the OPENXML function but the XML contains more than 150
> tables and I can't write the schema of all of them. Is there any other
> way creating database structure from XML file and inserting its data?
>
> thanks
>
> *** Sent via Developersdex http://www.droptable.com ***
>
| |
| George Ivanov 2005-10-27, 9:26 am |
| the only possible way I think will be using Bulk Load
but when i try to instancite SQLXMLBulkLoad object I can't find its
namespace. I have installed SQL Server 2000 with SQLXML 3.00 and add a
reference of Microsoft.Data.SqlXml but there is no SQLXMLBulkLoad object
in it.
Can you show some example of using Bulk Load with SQLXMLBulkLoad or any
other method?
thanks.
*** Sent via Developersdex http://www.droptable.com ***
| |
| Bertan ARI [MSFT] 2005-10-27, 9:26 am |
| I assume you are trying to use bulkload in .Net. Microsof.Data.SqlXml.dll is
not relevant to sqlxml functionality. Please, refer to the following
articles how to use bulkload .Net:
http://msdn2.microsoft.com/en-us/library/ms171878
http://msdn.microsoft.com/xml/defau.../exchsqlxml.asp
You said that you had 150 xml files and you don't want to specify schema for
every one of them so I'd like to note that Bulkload will require annotated
xsd for every xml file to import into Sql.
--
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"George Ivanov" <gsivanov@yahoo.com> wrote in message
news:uyfWp290FHA.3180@TK2MSFTNGP14.phx.gbl...
> the only possible way I think will be using Bulk Load
>
> but when i try to instancite SQLXMLBulkLoad object I can't find its
> namespace. I have installed SQL Server 2000 with SQLXML 3.00 and add a
> reference of Microsoft.Data.SqlXml but there is no SQLXMLBulkLoad object
> in it.
>
> Can you show some example of using Bulk Load with SQLXMLBulkLoad or any
> other method?
>
> thanks.
>
> *** Sent via Developersdex http://www.droptable.com ***
| |
|
| Try XMLengine. http://www.talinum.com/products.htm
James
"George Ivanov" wrote:
> How to import XML file in SQL, without knowing the structure of XML
> data. I tried the OPENXML function but the XML contains more than 150
> tables and I can't write the schema of all of them. Is there any other
> way creating database structure from XML file and inserting its data?
>
> thanks
>
> *** Sent via Developersdex http://www.droptable.com ***
>
|
|
|
|