Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

XML Workshop XII - Parsing a delimited string Expand / Collapse
Author
Message
Posted Wednesday, December 5, 2007 9:49 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:07 AM
Points: 11,157, Visits: 12,899

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
Posted Wednesday, December 5, 2007 10:28 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!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
Posted Tuesday, March 4, 2008 11:48 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Tuesday, March 4, 2008 10:35 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:42 AM
Points: 460, Visits: 2,523
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
Posted Wednesday, March 5, 2008 6:29 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Wednesday, March 5, 2008 6:30 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, March 18, 2010 10:36 AM
Points: 10, Visits: 25
Thank you
Post #464373
Posted Wednesday, March 5, 2008 7:45 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:42 AM
Points: 460, Visits: 2,523
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
Posted Wednesday, March 5, 2008 7:46 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:42 AM
Points: 460, Visits: 2,523
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
Posted Wednesday, March 5, 2008 10:59 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Wednesday, March 5, 2008 11:55 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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 »

Add to briefcase ««123»»

Permissions Expand / Collapse