January 6, 2010 at 6:30 am
Hi All,
I hae a requirement to read xml text which i am inserting from my application to table , it is a huge xml text it looks like.
<NewDataSet>
<Table>
<column 1>Data</column 1>
<column 2>Data</column 2>
.
.
.
<column 1500>Data</column 1500>
</Table>
<Table>
<column 1>Data</column 1>
<column 2>Data</column 2>
.
.
.
<column 1500>Data</column 1500>
</Table>
.
.
.
<Table>//10000 tables
<column 1>Data</column 1>
<column 2>Data</column 2>
.
.
.
<column 1500>Data</column 1500>
</Table>
</NewDataSet>
I have used openxml to read this xml string it is taking so much time,
can any one help me giving better one...
/********************************************************
Technology is just a tool,in terms of getting the kids working together and motivating them,the teacher is the most important.
********************************************************/
January 6, 2010 at 7:20 am
Beside your sample data are not wellformed xml data (name tag with spaces), I'd use XQuery instead of openxml:
SELECT
c.value('column1[1]','varchar(30)') as c1,
c.value('column2[1]','varchar(30)') as c2,
c.value('column1500[1]','varchar(30)') as c1500
FROM @xml.nodes('NewDataSet') a(b)
CROSS apply b.nodes('Table') t(c)
/* result set
c1c2c1500
DataDataData
DataDataData
DataDataData
*/
January 6, 2010 at 7:27 am
lmu92 (1/6/2010)
Beside your sample data are not wellformed xml data (name tag with spaces), I'd use XQuery instead of openxml:
SELECTc.value('column1[1]','varchar(30)') as c1,
c.value('column2[1]','varchar(30)') as c2,
c.value('column1500[1]','varchar(30)') as c1500
FROM @xml.nodes('NewDataSet') a(b)
CROSS apply b.nodes('Table') t(c)
/* result set
c1c2c1500
DataDataData
DataDataData
DataDataData
*/
This is posted in an SQL 2000 forum, and XQuery is a 2005/2008 feature not available in 2000.
To reply to the original question: Honestly, in SQL 2000, I'd move the XML parsing out of the database into a DLL. .NET languages are better at shredding XML than the tools you have for it in SQL 2000. Or, better yet, look into upgrading to SQL 2005 or 2008, where you can use XQuery, if that's an option.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 6, 2010 at 7:56 am
I keep forgetting to check the forum a question is posted in... Sorry!! :blush:
January 6, 2010 at 8:44 pm
Hi ,
The above solution using @xml.nodes for this data is very very slow compared to OpenXml, iam using sqlserver 2008 can you please suggest any other option because the aboce one is very very slow.
Thanks n advance
/********************************************************
Technology is just a tool,in terms of getting the kids working together and motivating them,the teacher is the most important.
********************************************************/
January 6, 2010 at 8:46 pm
Sorry for that the name tags i gave in the above example is the one wich i entered just to say that there are 1500 columns but i have proper names with out spaces and special char's
/********************************************************
Technology is just a tool,in terms of getting the kids working together and motivating them,the teacher is the most important.
********************************************************/
January 7, 2010 at 4:11 am
Where is your large xml file stored?
Now that we've clarified the version you're using, you could store the file in a table with an ID column and an xml data type column.
After importing the xml ile into that table, create an xml index on that xml column and use xquery again.
Should help a lot....
January 7, 2010 at 4:19 am
Hi ,
I have inserted that xml text into one table just because, to read it from some where instead of file. There will be always one row in the table which will be overridden when a new xml text comes. i dont think index for this purpose will add any value because it is always one row. or if you think for this purpose if i put the xml in file will help me alot i can change my code but only thing is it should load entire data from that xml in to my table in less than 5 seconds max. As of now i used SqlBulkCopy in .NET for this purpose to put data into my table but iam looking for any good and fastest way in sql.
/********************************************************
Technology is just a tool,in terms of getting the kids working together and motivating them,the teacher is the most important.
********************************************************/
January 7, 2010 at 4:42 am
What data type did you use to store the xml data? If the column is of xml data type, an index will be a HUGE help!
Straight from BOL:
An XML index is a shredded and persisted representation of the XML binary large objects (BLOBs) in the xml data type column. XML indexes are stored as internal tables.
Don't get confused between table index and xml index. An xml index will be efficient even if there's only one row in the table.
If you could provide your table definition as well as the indexes you have so far I could show you how to do it using your data instead of creating sample data myself.
January 7, 2010 at 4:57 am
Hi ,
I have created a table say "dummy" with one column say "xml" of type XML. I havent created any XML index on this table. I will create now and check that also.
/********************************************************
Technology is just a tool,in terms of getting the kids working together and motivating them,the teacher is the most important.
********************************************************/
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply