Dovberman
asked on
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-micr osoft-com: officedata " xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocat ion="AMEX_ 20131227.x sd" generated="2014-01-01T12:2 8:37">
<AMEX_20131227>
<AMEX_EOD_id>1</AMEX_EOD_i d>
<SymbolName>AA.P</SymbolNa me>
<QuoteDate>20131227</Quote Date>
<OpenPrice>76.55</OpenPric e>
<HighPrice>78.95</HighPric e>
<LowPrice>76.55</LowPrice>
<ClosePrice>78.95</ClosePr ice>
<TradeVolume>400</TradeVol ume>
</AMEX_20131227>
<AMEX_20131227>
<AMEX_EOD_id>2</AMEX_EOD_i d>
<SymbolName>AADR</SymbolNa me>
<QuoteDate>20131227</Quote Date>
<OpenPrice>37.6</OpenPrice >
<HighPrice>37.6</HighPrice >
<LowPrice>37.4</LowPrice>
<ClosePrice>37.4</ClosePri ce>
<TradeVolume>15800</TradeV olume>
</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
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-micr
<AMEX_20131227>
<AMEX_EOD_id>1</AMEX_EOD_i
<SymbolName>AA.P</SymbolNa
<QuoteDate>20131227</Quote
<OpenPrice>76.55</OpenPric
<HighPrice>78.95</HighPric
<LowPrice>76.55</LowPrice>
<ClosePrice>78.95</ClosePr
<TradeVolume>400</TradeVol
</AMEX_20131227>
<AMEX_20131227>
<AMEX_EOD_id>2</AMEX_EOD_i
<SymbolName>AADR</SymbolNa
<QuoteDate>20131227</Quote
<OpenPrice>37.6</OpenPrice
<HighPrice>37.6</HighPrice
<LowPrice>37.4</LowPrice>
<ClosePrice>37.4</ClosePri
<TradeVolume>15800</TradeV
</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
ASKER
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.
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.
You can convert your xml to a table by following code:
XmlTable function from my blog might helps.
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 )
XmlTable function from my blog might helps.
ASKER
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"
How do I refer to the path of the source xml table (@XMLData) which is on my hard drive.
"C:\MyFolder\AMEX_20131227
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you.
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.