query with xml for big data - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

query with xml for big data

Author

Topic

djokerss
Starting Member

28 Posts

Posted - 2005-10-26 : 05:03:59

i have query like this :

DECLARE @idoc int
DECLARE @doc varchar(8000)
SET @doc ='
<ROWDATA>
<ROW username="ADMIN" userpwd="u#129;}‡}u„" usercek="ADMINu#129;}‡}u„" userakses="1111111000000000000011111111001111111111000000000011110000001111111111111000000000000000000000000000" userid="1"/>
<ROW username="USER" userpwd="igYf" usercek="USERigYf" userakses="1111111000000000000011111111001111111111000000000011110000001111111111111000000000000000000000000000" userid="2"/>
<ROW username="KASIR" userpwd="EFG" usercek="KASIREFG" userakses="0000000000000000000000001000001000000011000000000011100000000000011101011000000000000000000000000000" userid="3"/>
<ROW username="PART" userpwd="EFG" usercek="PARTEFG" userakses="0000010000000000000000100100001111111011000000000011110000001111111111011000000000000000000000000000" userid="4"/>
<ROW username="CS" userpwd="EFG" usercek="CSEFG" userakses="1111100000000000000011011000001000000001000000000011110000001111111101011000000000000000000000000000" userid="5"/>
<ROW username="AKUN" userpwd="EFG" usercek="AKUNEFG" userakses="1111110000000000000000000000000000000111000000000011100000000000000000000000000000000000000000000000" userid="6"/>
</ROWDATA>'
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
-- Execute a SELECT statement that uses the OPENXML rowset provider.

SELECT *
FROM OPENXML (@idoc, '/ROWDATA/ROW',1)
WITH (username char(30),
userpwd char(30),usercek char(60),userakses har(100)

--------------
thats work for me...but now i have an xml containig data over 12000 ,
how about it...

Kristen
Test

22859 Posts

Posted - 2005-10-27 : 01:56:23

Should work fine. I do ad hoc XML stuff in Query Analyser (much like your example) and the last one had ... <fx:ToddlesOffToCheck> ... 32,000 lines of XML in it.

Kristen

djokerss
Starting Member

28 Posts

Posted - 2005-10-27 : 23:54:54

can u give me your sample ? thanks before

Kristen
Test

22859 Posts

Posted - 2005-10-28 : 07:11:39

What 32,000 lines of XML? I think not!

Did I misunderstand? Do you want to run your example above, but substituting a really large piece of XML instead of the small XML part of the script above? If so that will work fine in Query Analyser IME.

Kristen

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2005-10-28 : 08:10:30

Kristen, I'm no expert, but...
given that the largest local variable you can declare is varchar(8000), surely you'd have to put the literal string with the XML in it in the call to sp_xml_preparedocument?
Or am I missing something?


DECLARE @idoc int
EXEC sp_xml_preparedocument @idoc OUTPUT, '
<ROWDATA>
<!-- your data here -->
</ROWDATA>'

Kristen
Test

22859 Posts

Posted - 2005-10-28 : 08:42:42

Sorry, misread the original post.

Arnold you are quite right, and that is indeed what we do, sorry djokerss if I confused you.

Instead of:


DECLARE @idoc int
DECLARE @doc varchar(8000)
SET @doc ='
<ROWDATA>
...
</ROWDATA>'
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

use


DECLARE @idoc int
EXEC sp_xml_preparedocument @iDoc OUTPUT, 
	'<ROWDATA>
             ...
         </ROWDATA>'

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources