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
Solved

Import XML File into MS SQL Server

Posted on 2014-01-01
6
1,563 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 20

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

856 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