XML Workshop XII - Parsing a delimited string

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Jacob Sebastian » XML Workshop XII - Parsing a delimited string...

23 posts, Page 2 of 3

««1 2 3 »»

XML Workshop XII - Parsing a delimited string

Rate Topic

Display Mode

Topic Options

Author

Message

Jack Corbett

Posted Wednesday, December 05, 2007 9:49 AM

SSChampion

Group: General Forum Members
Last Login: Friday, May 17, 2013 12:22 PM
Points: 10,571, Visits: 11,871

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

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

Post #429845

mojo-168709

Posted Wednesday, December 05, 2007 10:28 AM

SSC Eights!

Group: General Forum Members
Last Login: Tuesday, January 15, 2013 10:46 AM
Points: 911, 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

Post #429855

Argneka

Posted Tuesday, March 04, 2008 11:48 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, March 18, 2010 10:36 AM
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

Post #463974

jacob sebastian

Posted Tuesday, March 04, 2008 10:35 PM

SSC-Addicted

Group: General Forum Members
Last Login: Saturday, May 11, 2013 8:17 AM
Points: 460, Visits: 2,521

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.

.

Post #464213

Argneka

Posted Wednesday, March 05, 2008 6:29 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, March 18, 2010 10:36 AM
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

Post #464372

Argneka

Posted Wednesday, March 05, 2008 6:30 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, March 18, 2010 10:36 AM
Points: 10, Visits: 25

Thank you

Post #464373

jacob sebastian

Posted Wednesday, March 05, 2008 7:45 AM

SSC-Addicted

Group: General Forum Members
Last Login: Saturday, May 11, 2013 8:17 AM
Points: 460, Visits: 2,521

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

.

Post #464439

jacob sebastian

Posted Wednesday, March 05, 2008 7:46 AM

SSC-Addicted

Group: General Forum Members
Last Login: Saturday, May 11, 2013 8:17 AM
Points: 460, Visits: 2,521

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

.

Post #464443

Argneka

Posted Wednesday, March 05, 2008 10:59 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, March 18, 2010 10:36 AM
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.

Post #464623

Argneka

Posted Wednesday, March 05, 2008 11:55 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, March 18, 2010 10:36 AM
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

Post #464664

« Prev Topic | Next Topic »

23 posts, Page 2 of 3

««1 2 3 »»

Permissions