Load XML with nested tables into SQL Server

  • Thread starter Thread starter Frank Uray
  • Start date Start date
F

Frank Uray

Hi all

I have problems loading XML data into SQL Server.
What is the best way to go ? I am new in using XML documents.
I have tried to do it with "Integration Services" and
also with "XmlTextReader", "XPathDocument", "DataSet.ReadXML" ...

None of this is working well because my XML document
has nested tables and in XML documents I dont see any
relations ...

Thanks for any comment .

Best regards
Frank

Here is what the XML looks like:

<?xml version="1.0" encoding="iso-8859-1" ?>
- <Test>
- <Transactions>
- <Trx>
<TrxCol1>false</TrxCol1>
<TrxCol2>SWAP</TrxCol2>
- <calendars>
<calendar>CHF</calendar>
</calendars>
- <legs>
- <leg>
<LegCol1>true</LegCol1>
- <calendars>
<calendar>JPY</calendar>
<calendar>GBP</calendar>
</calendars>
<LegCol2>false</LegCol2>
- <fixings>
- <fixing>
<FixingCol1>0</FixingCol1>
<FixingCol2>2007-03-19</FixingCol2>
</fixing>
- <fixing>
<FixingCol1>0</FixingCol1>
<FixingCol2>2007-03-20</FixingCol2>
</fixing>
- <fixing>
<FixingCol1>0</FixingCol1>
<FixingCol2>2007-03-21</FixingCol2>
</fixing>
- <fixing>
<FixingCol1>0</FixingCol1>
<FixingCol2>2007-03-22</FixingCol2>
</fixing>
</fixings>
</leg>
</legs>
<nr>XY88.6</nr>
</Trx>
- <Trx>
<TrxCol1>true</TrxCol1>
<TrxCol2>BOND</TrxCol2>
- <calendars>
<calendar>EUR</calendar>
</calendars>
- <legs>
- <leg>
<LegCol1>true</LegCol1>
- <calendars>
<calendar>USD</calendar>
<calendar>GBP</calendar>
</calendars>
<LegCol2>false</LegCol2>
- <fixings>
- <fixing>
<FixingCol1>0</FixingCol1>
<FixingCol2>2007-04-19</FixingCol2>
</fixing>
- <fixing>
<FixingCol1>0</FixingCol1>
<FixingCol2>2007-04-20</FixingCol2>
</fixing>
- <fixing>
<FixingCol1>0</FixingCol1>
<FixingCol2>2007-04-21</FixingCol2>
</fixing>
- <fixing>
<FixingCol1>0</FixingCol1>
<FixingCol2>2007-04-22</FixingCol2>
</fixing>
</fixings>
</leg>
</legs>
<nr>XY88.6</nr>
</Trx>
</Transactions>
</Test>
 
It will be easier to understand your problem if you describe the tables. I
think problem is becuase the way calendars is related to Transactions and
legs. I think if you remove calendars from the xml file then you can use
dataset.ReadXMLSchema and dataset.ReadXML to load the data.
You might have to create an empty dataset by querying your tables first and
then try to use dataset.ReadXML to load the data.
 
Back
Top