How to insert xml data into sql

<FILE>
<TITLE> Final Fuel Tax Rates </TITLE>
<QUARTER>1Q2010</QUARTER>
<RECORD>
<JURISDICTION ID="#16">AB</JURISDICTION>
<COUNTRY>CAN</COUNTRY>
<FUEL_TYPE>Gasoline</FUEL_TYPE>
<RATE COUNTRY="US" RATECHANGE="0">0.3217</RATE>
<RATE COUNTRY="CAN" RATECHANGE="0">0.0900</RATE>
<FUEL_TYPE>Special Diesel</FUEL_TYPE>
<RATE COUNTRY="US" RATECHANGE="0">0.3217</RATE>
<RATE COUNTRY="CAN" RATECHANGE="0">0.0900</RATE>
<FUEL_TYPE>Gasohol</FUEL_TYPE>
<RATE COUNTRY="US" RATECHANGE="0">0.3217</RATE>
<RATE COUNTRY="CAN" RATECHANGE="0">0.0900</RATE>
<FUEL_TYPE>Propane</FUEL_TYPE>
<RATE COUNTRY="US" RATECHANGE="0">0.2323</RATE>
<RATE COUNTRY="CAN" RATECHANGE="0">0.0650</RATE>
<FUEL_TYPE>LNG</FUEL_TYPE>
<RATE COUNTRY="US" RATECHANGE="0">0.0000</RATE>
<RATE COUNTRY="CAN" RATECHANGE="0">0.0000</RATE>
<FUEL_TYPE>CNG</FUEL_TYPE>
<RATE COUNTRY="US" RATECHANGE="0">0.0000</RATE>
<RATE COUNTRY="CAN" RATECHANGE="0">0.0000</RATE>
<FUEL_TYPE>Ethanol</FUEL_TYPE>
<RATE COUNTRY="US" RATECHANGE="0">0.3217</RATE>
<RATE COUNTRY="CAN" RATECHANGE="0">0.0900</RATE>
<FUEL_TYPE>Methanol</FUEL_TYPE>
<RATE COUNTRY="US" RATECHANGE="0">0.3217</RATE>
<RATE 开发者_运维百科COUNTRY="CAN" RATECHANGE="0">0.0900</RATE>
<FUEL_TYPE>E-85</FUEL_TYPE>
<RATE COUNTRY="US" RATECHANGE="0">0.3217</RATE>
<RATE COUNTRY="CAN" RATECHANGE="0">0.0900</RATE>
<FUEL_TYPE>M-85</FUEL_TYPE>
<RATE COUNTRY="US" RATECHANGE="0">0.3217</RATE>
<RATE COUNTRY="CAN" RATECHANGE="0">0.0900</RATE>
<FUEL_TYPE>A55</FUEL_TYPE>
<RATE COUNTRY="US" RATECHANGE="0">0.3217</RATE>
<RATE COUNTRY="CAN" RATECHANGE="0">0.0900</RATE>
<FUEL_TYPE>Biodiesel</FUEL_TYPE>
<RATE COUNTRY="US" RATECHANGE="0">0.3217</RATE>
<RATE COUNTRY="CAN" RATECHANGE="0">0.0900</RATE>
</RECORD>
<RECORD>

---

Some SQL DB support a XML type (you can put some XML on some cells). PostgreSQL has this fir example. The SQL/XML par of the standard has some things about XML in databases. Anyway, you might want to store the XML as a large binary object (or event as a large string ...). I don't know it that's what you are asking though.

If you want to do queries on the XML some SQL DB can extract information from XML types using XPath. Otherwise you'll have to map the data in the database which is more likely what you want but is not really related to XML but to SQL schema design.

Oh and I think the XML is not really well designed. It would be more logical to group each fuel_type and its rate in a"fuel" element.

---

solution is

XmlDocument _xmlDoc = new XmlDocument();
_xmlDoc.Load(@"C:\Users\AppData\Local\Temp\ry.xml");

string _xml = _xmlDoc.InnerXml;

FILE _fileObj = (FILE)DeSerialize(_xml, typeof(FILE));

if (_fileObj != null && _fileObj.RECORD != null && _fileObj.RECORD.Count > 0)
{
    foreach (FILERECORD _record in _fileObj.RECORD)
    {
        _record.JURISDICTION[0].Value;
        int i = 0;
        foreach (FILERECORDFUEL_TYPE _fuelType in _record.FUEL_TYPE)
        {   }
    }
}