XML File to SQL Server2005

Question

XML File to SQL Server2005

eslam.amgad

SSC Veteran

Points: 210
More actions
January 29, 2009 at 12:58 am

#134088

Hi all,
I need to convert an XML file to SQL Server2005 tables.
I was doing this from Access 2007 from External Data -> XML File.
Note: By Access database conversion I got two tables from this XML file.
I need to do the same thing in sql server 2005 with this XML file.
THANKS IN ADVANCE 🙂

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

jacob sebastian SSChampion Points: 11822 More actions · Answer 1

You might require an XSD, either as a separate file or embedded into the XML document. Could you post a sample XML document?

.

eslam.amgad SSC Veteran Points: 210 More actions · Answer 2

Sorry for being late,

I Attached a txt file which contains the contents of my .xml file (because i couldnt upload an xml file here).

if i import this file to MS Access it gives me 2 tables(solution and x_solution)

Russel Loski SSCertifiable Points: 7054 More actions · Answer 3

This will convert your file into result sets that you could load into a database. What I would do is to convert it to a stored procedure.

I added the encoding when I tried to load the file in SSIS. I couldn't load it with SSIS since there were two element lists under the node (solution, x_solution).

Russel Loski, MCSE Business Intelligence, Data Platform

eslam.amgad SSC Veteran Points: 210 More actions · Answer 4

Dear Russel Loski,

Thank you very very much for your effort and your work.

but because i found it beter for me to run this issue from my application, i.e my code,

I found this idea it solved my problem

DataSet reportData = new DataSet();

reportData.ReadXml("my File Path");

SqlConnection connection = new SqlConnection(Properties.Settings.Default.testConnectionString);

SqlBulkCopy sbc = new SqlBulkCopy(connection);

sbc.DestinationTableName = tblName;

if your DB col names don’t match your XML element names 100%

then relate the source XML elements (1st param) with the destination DB cols

sbc.ColumnMappings.Add("lemmaID", "lemma_ID");

sbc.ColumnMappings.Add("voc", "voc_");

sbc.ColumnMappings.Add("pos", "pos_");

sbc.ColumnMappings.Add("gloss", "gloss_");

connection.Open();

//table 3 is the required table in this dataset

sbc.WriteToServer(reportData.Tables[3]);

connection.Close();

I converted the xml to dataset then write the chosen dataset table to the database.

Thank you Russel again

NYCnewbie Valued Member Points: 63 More actions · Answer 5

Here's a challenge for you. We receive an XML file from another organization, sample here:

<dataset
xmlns="http://developer.cognos.com/schemas/xmldata/1/"
xmlns:xs="http://www.w3.org/2001/XMLSchema-instance"
xs:schemaLocation="http://developer.cognos.com/schemas/xmldata/1/ xmldata.xsd"
>
-->
<metadata>
<item name="ENTITY_NAM" type="xs:string" length="122"/>
<item name="TRADE_NAM" type="xs:string" length="122"/>
<item name="prem_num_and_strt" type="xs:string" length="92"/>
<item name="F1_1019_PREM_BLDG" type="xs:string" length="26"/>
</metadata>
<data>
<row>
<value>" INC.</value>
<value>RESTAURANT</value>
<value>61 STREET</value>
</row>
<row>
<value>RESTAURANT CORP</value>
<value xs:nil="true" />
<value>94 BOULEVARD</value>
</row>
</data>
</dataset>

In reality, there are over 23,000 records, and each row has 23 fields, but you can get the picture from the above. Anyway, given that there isn't a properly coded XSD, and each field is defined as "value", how can I get this into a SQL Server? When I use VS to create an XSD, it can't create a properly coded one based on this XML file.