[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Bulk sql upload of XML

Posted on 2010-11-22
11
Medium Priority
?
661 Views
Last Modified: 2013-01-25
hello experts,

I would like to transfer xml values to SQL server 2008 I am unsure on how to do so. the XML schema is below

<Orders>
  <OrderItems Product_ID="26" ProductName="" ProductDesc="Geospatial Experts" ProductPrice="" />
  <OrderItems Product_ID="2" ProductName="" ProductDesc="ESRI ArcGIS 9.3 ArcMap" ProductPrice="" />
  <OrderItems Product_ID="7" ProductName="" ProductDesc="ESRI ArcGIS 9.3 ArcCatalog" ProductPrice="" />
  <OrderItems Product_ID="8" ProductName="" ProductDesc="ESRI ArcGIS 9.3 ArcToolbox (ArcMap)" ProductPrice="" />
  <OrderItems Product_ID="10" ProductName="" ProductDesc="ESRI ArcGIS 9.3 ArcToolbox (ArcCatalog)" ProductPrice="" />
  <OrderItems Product_ID="11" ProductName="" ProductDesc="ESRI ArcGIS 9.3 Desktop Administrator" ProductPrice="" />
  <OrderItems Product_ID="17" ProductName="" ProductDesc="Quantum GIS" ProductPrice="" />
  <OrderItems Product_ID="22" ProductName="" ProductDesc="Microsoft Office 2010" ProductPrice="" />
  <OrderItems Product_ID="23" ProductName="" ProductDesc="Microsoft Windows Server 2008 R2" ProductPrice="" />
</Orders>

Open in new window

0
Comment
Question by:XGIS
[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
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 3

Expert Comment

by:kraiven
ID: 34187930
Either convert to a DataTable, or pass through a DataReader.

Instantiate a SqlBulkCopy object

Call WriteToServer on the instance passing it your DataTable or your Reader.

Note: Column names will have to match, but you can manipulate this if they don't already.
0
 
LVL 7

Author Comment

by:XGIS
ID: 34189441
Hello Kraiven:

Would you have a working example of this and will the node attributes impact the process?
0
 
LVL 1

Accepted Solution

by:
TDangler earned 750 total points
ID: 34189474
Or if you want to do it directly with SQL, try something like this,

(Assuming you have a table Orders(Product_ID int, ProductName varchar(32),  ProductDesc varchar(max), ProductPrice money)

you can go something the attached code...
 
declare @myXml xml = '
<Orders>
  <OrderItems Product_ID="26" ProductName="" ProductDesc="Geospatial Experts" ProductPrice="" />
  <OrderItems Product_ID="2" ProductName="" ProductDesc="ESRI ArcGIS 9.3 ArcMap" ProductPrice="" />
  <OrderItems Product_ID="7" ProductName="" ProductDesc="ESRI ArcGIS 9.3 ArcCatalog" ProductPrice="" />
  <OrderItems Product_ID="8" ProductName="" ProductDesc="ESRI ArcGIS 9.3 ArcToolbox (ArcMap)" ProductPrice="" />
  <OrderItems Product_ID="10" ProductName="" ProductDesc="ESRI ArcGIS 9.3 ArcToolbox (ArcCatalog)" ProductPrice="" />
  <OrderItems Product_ID="11" ProductName="" ProductDesc="ESRI ArcGIS 9.3 Desktop Administrator" ProductPrice="" />
  <OrderItems Product_ID="17" ProductName="" ProductDesc="Quantum GIS" ProductPrice="" />
  <OrderItems Product_ID="22" ProductName="" ProductDesc="Microsoft Office 2010" ProductPrice="" />
  <OrderItems Product_ID="23" ProductName="" ProductDesc="Microsoft Windows Server 2008 R2" ProductPrice="" />
</Orders>'


Insert into Orders
	 select c.value('@Product_ID', 'int'), c.value('@ProductName','varchar(32)'), c.value('@ProductDesc','varchar(max)'), c.value('@ProductPrice','money')
       from @myXml.nodes('/Orders/OrderItems') T(c)

Open in new window

0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 7

Author Comment

by:XGIS
ID: 34189668
Hello TDangler:

so you are suggestion to do a regular insert statement and use the xml as the source?? I am not sure where declare is supposed to be as I have never used it in C# only sql stored procedures. and it will need to load a file rather than be hard coded.
0
 
LVL 1

Assisted Solution

by:TDangler
TDangler earned 750 total points
ID: 34189716
Hi XGIS,

I'm just suggesting you simply run that code I posted directly in SQL Management Studio (Or whateer your SQL Client software is)..

That is if this is something you only have to do once.

If you have to do it regularly, I would suggest using kraiven's solution, or createing a Sproc that does the above Xml-parse and insert...  
create procedure Save_Xml
@myXml	xml
as 

begin

Insert into Orders
	 select c.value('@Product_ID', 'int'), c.value('@ProductName','varchar(32)'), c.value('@ProductDesc','varchar(max)'), c.value('@ProductPrice','money')
       from @myXml.nodes('/Orders/OrderItems') T(c)
       
end

Open in new window


Then you can pass your Xml as a string SqlParameter to a SqlCommand..

And so on...
0
 
LVL 3

Expert Comment

by:kraiven
ID: 34189859
Hi XGIS,

Sorry I did make the - probably unreasonable - assumption that you were running the insert from .Net code. TDangler's solution is good, but it is not a bulk insert; if your XML is many ten's of thousands of rows you might well experience performance issues. Look at the MSDN page for BULK INSERT from XML for an example of Bulk inserting from XML data.
If you are interested in doing the bulk insert from .Net then let me know and I can post some sample code probably tomorrow.
0
 
LVL 7

Author Comment

by:XGIS
ID: 34190063
Hello Kraiven:

yes I intend to run it via .net code C#, in a web app, the xml is stored on the clients pc within a cookie and when a button is clicked the xml in the coockie needs to be saved in a SQL database where the table has the schema as the xml.. I am thinking that maybe the datatable option is a good one... or even read directly from the cookie via
 HttpCookie customerXmlDataCookie= Request.Cookies["CustomerXmlData"];
            if( customerXmlDataCookie != null )
            {
                        // put your logic here
            }

Open in new window

0
 
LVL 3

Expert Comment

by:kraiven
ID: 34190175
OK, as I said I'll put together a short example tomorrow. In the meantime, if you're not familiar already, take a look at DataReaders and the System.Data.SqlBulkCopy class.
0
 
LVL 7

Author Comment

by:XGIS
ID: 34190370
No problem sounds wonderful thanks, I am rather familiar with sqlbulkcopy class as I used it a couple of years ago to transfer uploaded geodatabases to sql, the only thing i am concerned about is the node attributes on my current xml file
0
 
LVL 1

Expert Comment

by:pankajgharge
ID: 34202600
In CSharp, create a DataSet from XML and the upload the dataset by using SqlBulkCopy class to SQL server....
else
solution provided by TDangler looks great if you want to use Managemant Studio.
0
 
LVL 7

Author Closing Comment

by:XGIS
ID: 34329271
I ended up scraping the xml thing as sql session was better than xml and since there was no xml this question became redundant, sorry guys and thanks for your valuable time and input
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

650 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