Load XML with nested tables into SQL Server

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • =?Utf-8?B?RnJhbmsgVXJheQ==?=
    #1

    Load XML with nested tables into SQL Server

    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 "Integratio n Services" and
    also with "XmlTextReader" , "XPathDocument" , "DataSet.ReadXM L" ...

    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>fals e</TrxCol1>
    <TrxCol2>SWAP </TrxCol2>
    - <calendars>
    <calendar>CHF </calendar>
    </calendars>
    - <legs>
    - <leg>
    <LegCol1>true </LegCol1>
    - <calendars>
    <calendar>JPY </calendar>
    <calendar>GBP </calendar>
    </calendars>
    <LegCol2>fals e</LegCol2>
    - <fixings>
    - <fixing>
    <FixingCol1>0 </FixingCol1>
    <FixingCol2>200 7-03-19</FixingCol2>
    </fixing>
    - <fixing>
    <FixingCol1>0 </FixingCol1>
    <FixingCol2>200 7-03-20</FixingCol2>
    </fixing>
    - <fixing>
    <FixingCol1>0 </FixingCol1>
    <FixingCol2>200 7-03-21</FixingCol2>
    </fixing>
    - <fixing>
    <FixingCol1>0 </FixingCol1>
    <FixingCol2>200 7-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>fals e</LegCol2>
    - <fixings>
    - <fixing>
    <FixingCol1>0 </FixingCol1>
    <FixingCol2>200 7-04-19</FixingCol2>
    </fixing>
    - <fixing>
    <FixingCol1>0 </FixingCol1>
    <FixingCol2>200 7-04-20</FixingCol2>
    </fixing>
    - <fixing>
    <FixingCol1>0 </FixingCol1>
    <FixingCol2>200 7-04-21</FixingCol2>
    </fixing>
    - <fixing>
    <FixingCol1>0 </FixingCol1>
    <FixingCol2>200 7-04-22</FixingCol2>
    </fixing>
    </fixings>
    </leg>
    </legs>
    <nr>XY88.6</nr>
    </Trx>
    </Transactions>
    </Test>


    Tags: None
    • Gugale at Lincoln
      #2
      Re: Load XML with nested tables into SQL Server

      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.ReadXML Schema 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.

      "Frank Uray" <FrankUray@disc ussions.microso ft.comwrote in message
      news:A2B8981F-D2F3-49B0-B489-39935E25A98A@mi crosoft.com...
      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 "Integratio n Services" and
      also with "XmlTextReader" , "XPathDocument" , "DataSet.ReadXM L" ...
      >
      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>fals e</TrxCol1>
      <TrxCol2>SWAP </TrxCol2>
      - <calendars>
      <calendar>CHF </calendar>
      </calendars>
      - <legs>
      - <leg>
      <LegCol1>true </LegCol1>
      - <calendars>
      <calendar>JPY </calendar>
      <calendar>GBP </calendar>
      </calendars>
      <LegCol2>fals e</LegCol2>
      - <fixings>
      - <fixing>
      <FixingCol1>0 </FixingCol1>
      <FixingCol2>200 7-03-19</FixingCol2>
      </fixing>
      - <fixing>
      <FixingCol1>0 </FixingCol1>
      <FixingCol2>200 7-03-20</FixingCol2>
      </fixing>
      - <fixing>
      <FixingCol1>0 </FixingCol1>
      <FixingCol2>200 7-03-21</FixingCol2>
      </fixing>
      - <fixing>
      <FixingCol1>0 </FixingCol1>
      <FixingCol2>200 7-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>fals e</LegCol2>
      - <fixings>
      - <fixing>
      <FixingCol1>0 </FixingCol1>
      <FixingCol2>200 7-04-19</FixingCol2>
      </fixing>
      - <fixing>
      <FixingCol1>0 </FixingCol1>
      <FixingCol2>200 7-04-20</FixingCol2>
      </fixing>
      - <fixing>
      <FixingCol1>0 </FixingCol1>
      <FixingCol2>200 7-04-21</FixingCol2>
      </fixing>
      - <fixing>
      <FixingCol1>0 </FixingCol1>
      <FixingCol2>200 7-04-22</FixingCol2>
      </fixing>
      </fixings>
      </leg>
      </legs>
      <nr>XY88.6</nr>
      </Trx>
      </Transactions>
      </Test>
      >
      >

        Comment

        Previous template Next
        Working...