Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


XML Workshop XII - Parsing a delimited string


XML Workshop XII - Parsing a delimited string

Author
Message
Jack Corbett
  Jack Corbett
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11044 Visits: 14858

I agree it's an interesting "concept", but in the real world; where I have to process MILLIONS of records in a routine, I don't see it working....

jacob sebastian (12/5/2007)
I dont think this approach is good for Large Chunks of data. It is handy when you have a small piece of delimited string and you want to break it into a relational table quickly.


As Jacob says this may not be the best way to handle large sets of data particularly for importing, BCP is designed for that. But for a list being passed as a parameter to a stored procedure this is an excellent idea.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
mojo-168709
mojo-168709
SSC Eights!
SSC Eights! (923 reputation)SSC Eights! (923 reputation)SSC Eights! (923 reputation)SSC Eights! (923 reputation)SSC Eights! (923 reputation)SSC Eights! (923 reputation)SSC Eights! (923 reputation)SSC Eights! (923 reputation)

Group: General Forum Members
Points: 923 Visits: 831
To add the list of recommended readings regarding parsing a delimited string, I find Erland Sommarskog's articles are very helpful:

http://www.sommarskog.se/arrays-in-sql-2005.html
http://www.sommarskog.se/arrays-in-sql-2000.html
Argneka
Argneka
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 25
Hi,

How would I iterate through an XML object to obtain each specific row 1 at a time. I was trying this way, but value requires a literal for both parameters. Not sure what to do?

ALTER PROCEDURE [dbo].[XMLUpdateTest]
-- Add the parameters for the stored procedure here
@XmlList XML,
@RowCount int,

AS
BEGIN
Declare @Count int
Declare @ObjectName varchar(50)
Declare @ColumnName varchar(50)
Declare @Property varchar(50)
Declare @Value varchar(50)

Declare @ParObjectName varchar(50)
Declare @ParColumnName varchar(50)
Declare @ParProperty varchar(50)
Declare @ParValue varchar(50)

set @Count=0

while(@Count<@RowCount)
BEGIN

set @ParObjectName=@ObjectName+'['+convert(varchar(2), @Count)+']' --@ObjectName[3]

set @ParColumnName=@ColumnName+'['+convert(varchar(2), @Count)+']' --@ObjectName[3]

set @ParProperty=@Property+'['+convert(varchar(2), @Count)+']' --@ObjectName[3]

set @ParValue=@Value+'['+convert(varchar(2), @Count)+']' --@ObjectName[3]

Select XmlList.Row.value(@ParObjectName,'varchar(50)'),
XmlList.Row.value(@ParColumnName,'varchar(50)'),
XmlList.Row.value(@ParProperty,'varchar(50)'),
XmlList.Row.value(@ParValue,'varchar(50)')

from @XmlList.nodes('//Rows/Row') as XmlList(Row)



set @Count=@Count +1

END


Thanks for the help
jacob sebastian
jacob sebastian
SSC-Addicted
SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)

Group: General Forum Members
Points: 472 Visits: 2523
You could do this by using a variable. You can refer to an element/attribute as 'sql:variable("@varname")'. Look for sql:variable in books online. I have covered this in "XML Workshop XVII - Writing a LOOP to process XML elements in TSQL". I see it in pending publication list. Hope it will be out in a week or two.

.
Argneka
Argneka
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 25
Hi,

Yes, I agree with parsing large amounts of data. In this case I have a datable in my C# app that I want to pass as an XML parameter. That all works fine. The table may have 15-20 rows. The part I am having diffuculty with is how to iterate the XML table that is passed to the Stored Procedure. I guess I need to use the aliased table that I buid from the XML object. Is there a way to iterate through the XML object to obtain a specific element instead of aliasing another table?


Thanks,

Alan
Argneka
Argneka
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 25
Thank you
jacob sebastian
jacob sebastian
SSC-Addicted
SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)

Group: General Forum Members
Points: 472 Visits: 2523
Are you looking for something like the following?

DECLARE @x XML
SET @x =
'




















'

-- Total count of Nodes
DECLARE @max INT, @i INT
SELECT
@max = @x.query('
{ count(/Employees/Employee) }
').value('e[1]','int')

-- Set counter variable to 1
SET @i = 1

-- variable to store employee name
DECLARE @EmpName VARCHAR(10)

-- loop starts
WHILE @i <= @max BEGIN
-- select "Name" to the variable
SELECT
@EmpName = x.value('Name[1]', 'VARCHAR(20)')
FROM
@x.nodes('/Employees/Employee[position()=sql:variable("@i")]')
e(x)

-- print the name
PRINT @EmpName

-- increment counter
SET @i = @i + 1
END

.
jacob sebastian
jacob sebastian
SSC-Addicted
SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)

Group: General Forum Members
Points: 472 Visits: 2523
aplogies for the bad formatting. the editor does not help me to format correctly. I must be missing something. And my xml data is also missing in the post. But I guess this will help you to get an idea about writing loop to process XML elements

.
Argneka
Argneka
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 25
Hi,

Yes that may work. I was able to pass as a XML values as attributes. Then I just used a CURSOR - I know performance hog and this works OK. I will try your method as well.


Thanks,

Alan

PS: I will forward the final solution as soon as I get it.
Argneka
Argneka
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 25
Hi again,

I have it working, but I could not figure this part out. Query Analyzer throws an exception for XQUERY on this line of code:

SELECT
@max = @x.query('
{ count(/Employees/Employee) }
').value('e[1]','int')


Right now I am passing the count but would like to dynamically obtain based on your example.

Thanks,


Alan
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search