Solved

Import XML File into MS SQL Server

Posted on 2014-01-01
6
1,552 Views
Last Modified: 2014-01-05
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
0
Comment
Question by:Dovberman
  • 3
  • 2
6 Comments
 
LVL 19

Expert Comment

by:Patricksr1972
ID: 39750106
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.
0
 

Author Comment

by:Dovberman
ID: 39750135
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.
0
 
LVL 12

Expert Comment

by:Habib Pourfard
ID: 39750176
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.
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

Author Comment

by:Dovberman
ID: 39750207
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"
0
 
LVL 12

Accepted Solution

by:
Habib Pourfard earned 500 total points
ID: 39750693
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
0
 

Author Closing Comment

by:Dovberman
ID: 39757716
Thank you.
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how the fundamental information of how to create a table.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now