?
Solved

Import XML File into MS SQL Server

Posted on 2014-01-01
6
Medium Priority
?
1,644 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 23

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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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 2000 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

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

765 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