SQLServerCentral / Discuss Content Posted by Jacob Sebastian / Article Discussions / Article Discussions by Author / XML Workshop XII - Parsing a delimited string / Latest Posts

RE: XML Workshop XII - Parsing a delimited string

EWalter — Fri, 12 Sep 2008 09:54:17 GMT

I wrote a function very similar to this. I added some code to catch reserved characters ( ,")CREATE FUNCTION [dbo].[split]( @sInputList VARCHAR(max), @sDelimiter VARCHAR(50)) RETURNS @List TABLE (item VARCHAR(max)) BEGIN declare @x xml select @x=convert(xml,coalesce(' '+ replace( replace(replace(replace(@sInputList,' ','>'),'"','"') ,@sDelimiter,' ')) insert @List(item) select T.x.value('.','varchar(max)') as items from @x.nodes('/t') T(x);RETURN END

RE: XML Workshop XII - Parsing a delimited string

jacob sebastian — Thu, 06 Mar 2008 05:54:30 GMT

I see that the editor has deleted XML tags in my post. So the XQUERY that you asked me in the previous post should look like this: (i am replacing XML tages with [])SELECT @max = @x.query('[e] { count(/Employees/Employee) } [/e]').value('e[1]','int')

RE: XML Workshop XII - Parsing a delimited string

Argneka — Wed, 05 Mar 2008 12:58:32 GMT

Hi,This does exacally what I wanted it to do. Thanks for the insight. I have alot more work to do but the core is 100%.declare @XmlList XMLdeclare @count intdeclare @rows intset @count=1set @XMLList=' 'Declare @MasterObjectName varchar(50)Declare @MasterColumnName varchar(50)Declare @MasterProperty varchar(50)Declare @MasterValue varchar(50)declare @XmlCount varchar(3)Declare @ObjectName varchar(50)Declare @ColumnName varchar(50)Declare @Property varchar(50)Declare @Value varchar(50)select @XmlCount=CONVERT(varchar,@XmlList.query('count(/Rows/Row/.)'))set @rows=CONVERT(int,@XmlCount)select @rowsWHILE(@count<=@rows)BEGIN Select @ObjectName=Items.List.value('@ObjectName[1]','varchar(50)'), @ColumnName=Items.List.value('@ColumnName[1]','varchar(50)'), @Property=Items.List.value('@Property[1]','varchar(50)'), @Value=Items.List.value('@Value[1]','varchar(50)') from @XmlList.nodes('//Rows/Row[position()=sql:variable("@count")]') as Items(List)set @count=@count+1select @ObjectNameselect @ColumnNameselect @Propertyselect @ValueENDThanks,Alan

RE: XML Workshop XII - Parsing a delimited string

Argneka — Wed, 05 Mar 2008 11:55:44 GMT

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

RE: XML Workshop XII - Parsing a delimited string

Argneka — Wed, 05 Mar 2008 10:59:12 GMT

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,AlanPS: I will forward the final solution as soon as I get it.

RE: XML Workshop XII - Parsing a delimited string

jacob sebastian — Wed, 05 Mar 2008 07:46:47 GMT

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

RE: XML Workshop XII - Parsing a delimited string

jacob sebastian — Wed, 05 Mar 2008 07:45:37 GMT

Are you looking for something like the following? DECLARE @x XMLSET @x = ' ' -- Total count of NodesDECLARE @max INT, @i INTSELECT @max = @x.query(' { count(/Employees/Employee) } ').value('e[1]','int') -- Set counter variable to 1SET @i = 1 -- variable to store employee nameDECLARE @EmpName VARCHAR(10) -- loop startsWHILE @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

RE: XML Workshop XII - Parsing a delimited string

Argneka — Wed, 05 Mar 2008 06:30:16 GMT

Thank you

RE: XML Workshop XII - Parsing a delimited string

Argneka — Wed, 05 Mar 2008 06:29:10 GMT

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

RE: XML Workshop XII - Parsing a delimited string

jacob sebastian — Tue, 04 Mar 2008 22:35:06 GMT

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.

RE: XML Workshop XII - Parsing a delimited string

Argneka — Tue, 04 Mar 2008 11:48:49 GMT

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,ASBEGINDeclare @Count intDeclare @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=0while(@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 +1ENDThanks for the help

RE: XML Workshop XII - Parsing a delimited string

mojo-168709 — Wed, 05 Dec 2007 10:28:07 GMT

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.htmlhttp://www.sommarskog.se/arrays-in-sql-2000.html

RE: XML Workshop XII - Parsing a delimited string

Jack Corbett — Wed, 05 Dec 2007 09:49:55 GMT

[quote]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....[/quote][quote][b]jacob sebastian (12/5/2007)[/b][hr]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.[/quote]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.

RE: XML Workshop XII - Parsing a delimited string

M Gnat — Wed, 05 Dec 2007 09:23:23 GMT

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....

RE: XML Workshop XII - Parsing a delimited string

Jack Corbett — Wed, 05 Dec 2007 06:17:08 GMT

I thought this was a very interesting article and certainly presented a new way to handle delimited strings without looping. For new applications I would just have the application pass XML as the parameter, but this is certainly a good way to handle existing applications and SSRS 2005 multi-select parameters.

RE: XML Workshop XII - Parsing a delimited string

jacob sebastian — Wed, 05 Dec 2007 06:14:33 GMT

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.

RE: XML Workshop XII - Parsing a delimited string

LP-181697 — Wed, 05 Dec 2007 06:10:12 GMT

This method is good. But it adding 6 additional characters to the real data value. It means, suddenly your string will not fit into allocated number of characters. You have to be careful.

RE: XML Workshop XII - Parsing a delimited string

Jeff Moden — Wed, 05 Dec 2007 06:09:04 GMT

[quote]Right in the middle of the million rows is a record or two that is mangled. Rejecting the file isn't an option. The import routine needs to be able to import all the good stuff and leave the bad stuff in a 'limbo' file for manual intervention.[/quote]BCP will do just that very nicely... second in speed only to Bulk Insert which does not have such a capability.

RE: XML Workshop XII - Parsing a delimited string

jacob sebastian — Wed, 05 Dec 2007 03:00:39 GMT

I understand the problem now. A schema will validate an entire document. I do not think that a schema can be used to filter "bad records" and process the "good" ones. I guess the only option available is to query the XML data and retrieve the "good" records (and retrieve the "bad" onese and dumb to a table or XML file for manual review).regardsJacob

RE: XML Workshop XII - Parsing a delimited string

Phil Factor — Wed, 05 Dec 2007 02:43:38 GMT

A typical problem I've had to face in the past might be that I've got a million or so rows of data from a switch that have to be imported. If they are not imported, then the business runs the risk of leaving a fraud or intrusion undetected. Right in the middle of the million rows is a record or two that is mangled. Rejecting the file isn't an option. The import routine needs to be able to import all the good stuff and leave the bad stuff in a 'limbo' file for manual intervention. Cleaning the data manually before import isn't a good idea either as such things usually are scheduled for the early hours of the morning when the server isn't so busy. Could a schema solve this sort of problem by filtering 'sheep-from-goats' on a record-by-record basis, rather than a document basis?I'd love to know what causes slow XML processing but, like you, I'll have to wait until it happens to me! I ran some timings a while back with the various parameter-passing techniques and found XML to be as fast as the 'helper-table'/'number table' technique, which is far faster than any iterative technique.

RE: XML Workshop XII - Parsing a delimited string

jacob sebastian — Wed, 05 Dec 2007 02:24:28 GMT

I agree with you on the first point. Wherever I used the XML approach in our applications, it just worked well. However, I have read some posts where people complained about performance problems. I am not sure if it is because they are using it in an incorrect manner or something else. Or it could be the volume of data...i am not sure.On the next point, do you think a schema could help? jacob

RE: XML Workshop XII - Parsing a delimited string

Phil Factor — Wed, 05 Dec 2007 02:16:34 GMT

I really enjoy these XML workshops. Of course, the parsing of delimited data is an important issue for data feeds. If data can be converted into XML first, using AWK or GREP or whatever, it then becomes much easier to gulp it into SQL Server. The biggest questions I've had are: [b]performance.[/b] Whenever I've used this XML technology for data feeds, or passing data between routines, it has been very fast, but others using a very similar system have reported it as being very slow. I'm not at all sure why the difference.[b]Resilience[/b] Maybe I've been unlucky, but I've had many feeds to deal with that occasionally spit out data that crashes the simplest, and most obvious, data import systems. I reckon that the best systems can isolate corrupt or incorrect data before the bulk insert and allow inspection of it by the DBAAny thoughts?

XML Workshop XII - Parsing a delimited string

jacob sebastian — Tue, 04 Dec 2007 22:29:51 GMT

Comments posted to this topic are about the item [B]XML Workshop XII - Parsing a delimited string[/B]