|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
xml file into tableI have a xml file which contains nested data.
for ex. each Order has multiple order details records. The data should go into Order table and Order Details table. How should I parse the xml file data so that it loads into two tables using stored procedure? This depends on a couple of things....
1. Which version of SQL Server are you using 2005, 2005 or earlier? 2. How big are are the XML files likely to get? The answers to these will help dictate a better answer, as the technologies available are completely different depending on the version of sql server. If you're using SQL 2005, there are several techniques available, but the best approach really depends on the size of the XML file and to some extent the amount of resources available to you on the server at the time that the file is processed. Regards Colin Dawson www.cjdawson.com Show quoteHide quote "UA" <usha.anum***@eds.com> wrote in message news:CF4ED226-3C98-4A2C-A0AD-8EB9FACB41B3@microsoft.com... >I have a xml file which contains nested data. > for ex. each Order has multiple order details records. > The data should go into Order table and Order Details table. > > How should I parse the xml file data so that it loads into two tables > using > stored procedure? Hi
You could use a staging table to load this data using BCP or OPENROWSET with the new BULK option. Then you can manipulate the data from there. There is an example of using OPENROWSET to load a text file in the OPENROWSET topic in Books Online, to load an XML file is very similar. John Show quoteHide quote "UA" wrote: > I have a xml file which contains nested data. > for ex. each Order has multiple order details records. > The data should go into Order table and Order Details table. > > How should I parse the xml file data so that it loads into two tables using > stored procedure? To be more clear on my question,
I have xml file which contains data like this <ROOT> <Customers CustomerID="VINET" ContactName="Paul Henriot"> <Orders CustomerID="VINET" EmployeeID="5" OrderDate= "1996-07-04T00:00:00"> <Order_x0020_Details OrderID="10248" ProductID="11" Quantity="12"/> <Order_x0020_Details OrderID="10248" ProductID="42" Quantity="10"/> </Orders> </Customers> <Customers CustomerID="LILAS" ContactName="Carlos Gonzlez"> <Orders CustomerID="LILAS" EmployeeID="3" OrderDate= "1996-08-16T00:00:00"> <Order_x0020_Details OrderID="10283" ProductID="72" Quantity="3"/> </Orders> </Customers> </ROOT> I want to load Customers data into Customer table and Orders data in Order table. As far as BCP goes I know it is useful for text file but I do not think you can import xml data into tables. If so, please give me the ex. I am using SQL Server 2005. I know in SSIS you have xml source task which can be used to load data directly. But other than that is there any other way to load data. Show quoteHide quote "John Bell" wrote: > Hi > > You could use a staging table to load this data using BCP or OPENROWSET with > the new BULK option. Then you can manipulate the data from there. There is an > example of using OPENROWSET to load a text file in the OPENROWSET topic in > Books Online, to load an XML file is very similar. > > John > > "UA" wrote: > > > I have a xml file which contains nested data. > > for ex. each Order has multiple order details records. > > The data should go into Order table and Order Details table. > > > > How should I parse the xml file data so that it loads into two tables using > > stored procedure? Hi
You should read books online and familiarise yourself with the new features available in SQL 2005 For more on BULK INSERT and BCP see: http://msdn2.microsoft.com/en-us/library/ms175915.aspx For BULK INSERTing XML files check out: http://msdn2.microsoft.com/en-us/library/ms191184.aspx If you need to query your XML to move it from the staging tables see the XQUERY support in SQL 2005 and the methods available for the XML Datatype http://msdn2.microsoft.com/en-us/library/ms190798.aspx For SQLXMLs XMLBulkload (as suggested by Dan) see: http://msdn2.microsoft.com/en-us/library/ms171993.aspx HTH John Show quoteHide quote "UA" wrote: > To be more clear on my question, > > I have xml file which contains data like this > <ROOT> > <Customers CustomerID="VINET" ContactName="Paul Henriot"> > <Orders CustomerID="VINET" EmployeeID="5" OrderDate= > "1996-07-04T00:00:00"> > <Order_x0020_Details OrderID="10248" ProductID="11" Quantity="12"/> > <Order_x0020_Details OrderID="10248" ProductID="42" Quantity="10"/> > </Orders> > </Customers> > <Customers CustomerID="LILAS" ContactName="Carlos Gonzlez"> > <Orders CustomerID="LILAS" EmployeeID="3" OrderDate= > "1996-08-16T00:00:00"> > <Order_x0020_Details OrderID="10283" ProductID="72" Quantity="3"/> > </Orders> > </Customers> > </ROOT> > > > I want to load Customers data into Customer table and Orders data in Order > table. > > As far as BCP goes I know it is useful for text file but I do not think you > can import xml data into tables. If so, please give me the ex. > > I am using SQL Server 2005. > I know in SSIS you have xml source task which can be used to load data > directly. > But other than that is there any other way to load data. > > > > > "John Bell" wrote: > > > Hi > > > > You could use a staging table to load this data using BCP or OPENROWSET with > > the new BULK option. Then you can manipulate the data from there. There is an > > example of using OPENROWSET to load a text file in the OPENROWSET topic in > > Books Online, to load an XML file is very similar. > > > > John > > > > "UA" wrote: > > > > > I have a xml file which contains nested data. > > > for ex. each Order has multiple order details records. > > > The data should go into Order table and Order Details table. > > > > > > How should I parse the xml file data so that it loads into two tables using > > > stored procedure? Assuming that it representative of the file size, you can perform an
OpenRowSet( BULK to load the file into a variable, cast it to XML then use the SQL XML datatype to query the document and insert into your tables. Regards Colin Dawson www.cjdawson.com Show quoteHide quote "UA" <usha.anum***@eds.com> wrote in message news:B4BBC63D-D69D-433E-BBCA-C10A913A0B22@microsoft.com... > To be more clear on my question, > > I have xml file which contains data like this > <ROOT> > <Customers CustomerID="VINET" ContactName="Paul Henriot"> > <Orders CustomerID="VINET" EmployeeID="5" OrderDate= > "1996-07-04T00:00:00"> > <Order_x0020_Details OrderID="10248" ProductID="11" Quantity="12"/> > <Order_x0020_Details OrderID="10248" ProductID="42" Quantity="10"/> > </Orders> > </Customers> > <Customers CustomerID="LILAS" ContactName="Carlos Gonzlez"> > <Orders CustomerID="LILAS" EmployeeID="3" OrderDate= > "1996-08-16T00:00:00"> > <Order_x0020_Details OrderID="10283" ProductID="72" Quantity="3"/> > </Orders> > </Customers> > </ROOT> > > > I want to load Customers data into Customer table and Orders data in Order > table. > > As far as BCP goes I know it is useful for text file but I do not think > you > can import xml data into tables. If so, please give me the ex. > > I am using SQL Server 2005. > I know in SSIS you have xml source task which can be used to load data > directly. > But other than that is there any other way to load data. > > > > > "John Bell" wrote: > >> Hi >> >> You could use a staging table to load this data using BCP or OPENROWSET >> with >> the new BULK option. Then you can manipulate the data from there. There >> is an >> example of using OPENROWSET to load a text file in the OPENROWSET topic >> in >> Books Online, to load an XML file is very similar. >> >> John >> >> "UA" wrote: >> >> > I have a xml file which contains nested data. >> > for ex. each Order has multiple order details records. >> > The data should go into Order table and Order Details table. >> > >> > How should I parse the xml file data so that it loads into two tables >> > using >> > stored procedure? Another technique not yet mentioned in this thread is with SQLXML, which has
a BulkLoad feature. SQLXML 4.0 is included with SQL 2005. SQLXML 3.0, which was released post-SQL2000 RTM is available as a separate download: http://www.microsoft.com/downloads/details.aspx?FamilyID=51D4A154-8E23-47D2-A033-764259CFB53B&displaylang=en -- Show quoteHide quoteHope this helps. Dan Guzman SQL Server MVP "UA" <usha.anum***@eds.com> wrote in message news:CF4ED226-3C98-4A2C-A0AD-8EB9FACB41B3@microsoft.com... >I have a xml file which contains nested data. > for ex. each Order has multiple order details records. > The data should go into Order table and Order Details table. > > How should I parse the xml file data so that it loads into two tables > using > stored procedure?  
Other interesting topics
How to implement transactions
"Order By" Parameter to a Stored Procedure 'System.OutOfMemoryException' ? Challenging query Views Vs Stored Procedures! Even after year of coding I ask... rows to column sql server 2000 Formatting and returning Time from Text Deferred drop Migrate data from Access to SQL 2005 |
|||||||||||||||||||||||
