Solved

Import XML File into MS SQL Server

Posted on 2014-01-01
6
1,572 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 22

Expert Comment

by:Patrick Bogers
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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that undeā€¦
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
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
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.

733 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