| Author |
Topic |
|
aawan
Starting Member
24 Posts |
 Posted - 2006-03-29 : 11:47:55
|
I have an XML file that I want to parse into name value pairs and eventually select certain items to insert into a table in SQL.Edit: I am running SQL Server 2000I have successfully used BULK INSERT to load the file into SQL Server.Like so:BULK INSERT [dbo].[XMLOriginal] FROM '\\myserver\d$\Inetpub\Intranet\RR\Data\myTest.xml'WITH ( DATAFILETYPE = 'char')The create table script for XMLOriginal is:CREATE TABLE [dbo].[XMLOriginal] ( [XMLVariables] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]So as you can see I'm loading one huge ntext field, since the xmlfile is really big.My question is that now that the XML has been dumped into one field in my database, what next?How can I parse the data?I am trying to use the sp_xml_preparedocument. However, I can't pass the XML to it, because when I try to select out the XMLVariables field from the XMLOriginal table, I can't set it to a variable declared as ntext. SQL throws an error that I can't declare local variables as 'text','ntext', etc. The data in XMLVariables exceeds the 8000 limit of varchar.Please help.Thanks,Ali |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
|
|
aawan
Starting Member
24 Posts |
Posted - 2006-03-29 : 12:49:33
|
| That's really not what I'm looking for. Since I'm not looking for a VBScript solution.If someone can help me, dealing with a table in the database that contains an entire XML string, and how to parse it out using OPENXML, I would really appreciate it. |
 |
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2006-03-29 : 14:35:44
|
| Using BULK INSERT a standard csv file is going to be much more efficient.Strip the xml out and use a flat file instead.rockmoose |
|
|
|
aawan
Starting Member
24 Posts |
Posted - 2006-03-29 : 15:57:50
|
| rockmoose,yeah that's my last resort.I just thought it would be cool to use some of SQL Server 2k's built-in XML features. But I guess that isn't possible.Thanks for everyone's comments so far.-AA |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2006-03-30 : 02:54:59
|
| You might look at SQLXMLBulkLoad utility. Or google for sp_xml_concat.rockmoose |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-03-30 : 03:53:40
|
| Have a look athttp://www.nigelrivett.net/SQLTsql/ParseXML.htmlEspecially the bit about the file size being divisible by 4.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
aawan
Starting Member
24 Posts |
Posted - 2006-03-30 : 11:59:50
|
| Thanks to NR and RockMoose. You listed some excellent resources to help breaking up a large file to import into SQL.Thanks for pointing me in the right direction. Actually sp_xml_concat is right on the money, in the opening paragraph it presents the exact question almost that I posted here. |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2006-03-30 : 14:44:22
|
quote:
Originally posted by aawan
Thanks to NR and RockMoose. You listed some excellent resources to help breaking up a large file to import into SQL.Thanks for pointing me in the right direction. Actually sp_xml_concat is right on the money, in the opening paragraph it presents the exact question almost that I posted here.
The thanks go to Mr. Ken Henderson whom I beleive is the father of the aforementioned sproc.____________________________________________________________Amazing the energy that goes into making xml actually work  rockmoose |
|
|
|
|
Problems parsing XML