XGIS
asked on
Bulk sql upload of XML
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
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>
ASKER
Hello Kraiven:
Would you have a working example of this and will the node attributes impact the process?
Would you have a working example of this and will the node attributes impact the process?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
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
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
}
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.
ASKER
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
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.
else
solution provided by TDangler looks great if you want to use Managemant Studio.
ASKER
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
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.