Import XML File into MS SQL Server

I have a XML table that needs to be imported into a SQL Server Database.
A new SQL server table can be created.  I prefer to append rows from the XML table to an existing SQL Server table.

This is part of the XML table:

<?xml version="1.0" encoding="UTF-8"?>
<dataroot xmlns:od="urn:schemas-microsoft-com:officedata" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"  xsi:noNamespaceSchemaLocation="AMEX_20131227.xsd" generated="2014-01-01T12:28:37">
<AMEX_20131227>
      <AMEX_EOD_id>1</AMEX_EOD_id>
      <SymbolName>AA.P</SymbolName>
      <QuoteDate>20131227</QuoteDate>
                 <OpenPrice>76.55</OpenPrice>
                 <HighPrice>78.95</HighPrice>
                 <LowPrice>76.55</LowPrice>
                 <ClosePrice>78.95</ClosePrice>
                 <TradeVolume>400</TradeVolume>
</AMEX_20131227>
<AMEX_20131227>
<AMEX_EOD_id>2</AMEX_EOD_id>
<SymbolName>AADR</SymbolName>
<QuoteDate>20131227</QuoteDate>
<OpenPrice>37.6</OpenPrice>
<HighPrice>37.6</HighPrice>
<LowPrice>37.4</LowPrice>
<ClosePrice>37.4</ClosePrice>
<TradeVolume>15800</TradeVolume>
</AMEX_20131227>


I know the path and filename of the XML file.

Either TSQL or a C# procedure would work for me.

I do not have bulk insert permissions.

What would you suggest?

Thanks
DovbermanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Patrick BogersDatacenter platform engineer LindowsCommented:
Hi

Just to be curious, what are you going to use this data for?

About importing XML to SQL there are a few steps which are described in the link.
DovbermanAuthor Commented:
This is one of several tables in the following Stock Pick app I have written.

http://www.stockpickermax.com/ 

As noted, I do not have bulk insert permissions. Currently, I am reading each row of a comma delimited file and executing a stored procedure 1800 times (once for each row).
This is time and resource consuming.
Use of the import task in SSMS is also time consuming.

I might be able to store the import task and edit the source filename and path.  The task needs to be run each day after the market closes.

From the link:
INSERT INTO XMLwithOpenXML(XMLData, LoadedDateTime)
SELECT CONVERT(XML, BulkColumn) AS BulkColumn, GETDATE()
FROM OPENROWSET(BULK 'D:\OpenXMLTesting.xml', SINGLE_BLOB) AS x;
 The syntax above indicates a need for Bulk insert.
Habib PourfardSoftware DeveloperCommented:
You can convert your xml to a table by following code:
DECLARE @XMLData XML
SET @XMLData = '<AMEX_20131227>
  <AMEX_EOD_id>1</AMEX_EOD_id>
  <SymbolName>AA.P</SymbolName>
  <QuoteDate>20131227</QuoteDate>
  <OpenPrice>76.55</OpenPrice>
  <HighPrice>78.95</HighPrice>
  <LowPrice>76.55</LowPrice>
  <ClosePrice>78.95</ClosePrice>
  <TradeVolume>400</TradeVolume>
</AMEX_20131227>
<AMEX_20131227>
  <AMEX_EOD_id>2</AMEX_EOD_id>
  <SymbolName>AADR</SymbolName>
  <QuoteDate>20131227</QuoteDate>
  <OpenPrice>37.6</OpenPrice>
  <HighPrice>37.6</HighPrice>
  <LowPrice>37.4</LowPrice>
  <ClosePrice>37.4</ClosePrice>
  <TradeVolume>15800</TradeVolume>
</AMEX_20131227>'

SELECT  T.col.value('AMEX_EOD_id[1]', 'int') AMEX_EOD_id,
        T.col.value('ClosePrice[1]', 'float') ClosePrice,
        T.col.value('HighPrice[1]', 'float') HighPrice,
        T.col.value('LowPrice[1]', 'float') LowPrice,
        T.col.value('OpenPrice[1]', 'float') OpenPrice,
        T.col.value('QuoteDate[1]', 'datetime') QuoteDate,
        T.col.value('SymbolName[1]', 'varchar(64)') SymbolName,
        T.col.value('TradeVolume[1]', 'int') TradeVolume
FROM    @XMLData.nodes('//AMEX_20131227') T ( col )

Open in new window



XmlTable function from my blog might helps.
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

DovbermanAuthor Commented:
I understand the statements.

How do I refer to the path of the source xml table (@XMLData) which is on my hard drive.

"C:\MyFolder\AMEX_20131227.XML"
Habib PourfardSoftware DeveloperCommented:
As you don't have permission for bulk insert you probably don't have permission for xp_cmdshell as well. so I think it's better to look for a C# solution.

here is a method that converts xml to DataSet. then you can update your database:
public static void DeserializeXmlFile()
        {
            string path = @"C:\MyFolder\AMEX_20131227.XML";

            DataSet ds = new DataSet();

            System.IO.FileStream fsReadXml = new System.IO.FileStream(path, System.IO.FileMode.Open);
            try
            {
                ds.ReadXml(fsReadXml);
            }
            finally
            {
                fsReadXml.Close();
            }
        }

Open in new window

AMEX-20131227.XML

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DovbermanAuthor Commented:
Thank you.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.