SQLServerCentral.com / Discuss Content Posted by Jacob Sebastian / Article Discussions / Article Discussions by Author / XML Workshop XII - Parsing a delimited string / Latest PostsInstantForum.NET v2.9.0SQLServerCentral.comhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 03 Mar 2015 12:26:16 GMT20RE: XML Workshop XII - Parsing a delimited stringhttp://www.sqlservercentral.com/Forums/Topic429626-356-1.aspxI 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,' ','&gt;'),'"','&quot;') ,@sDelimiter,' ')) insert @List(item) select T.x.value('.','varchar(max)') as items from @x.nodes('/t') T(x);RETURN END Fri, 12 Sep 2008 09:54:17 GMTEWalterRE: XML Workshop XII - Parsing a delimited stringhttp://www.sqlservercentral.com/Forums/Topic429626-356-1.aspxI 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') Thu, 06 Mar 2008 05:54:30 GMTjacob sebastianRE: XML Workshop XII - Parsing a delimited stringhttp://www.sqlservercentral.com/Forums/Topic429626-356-1.aspxHi,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&lt;=@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,AlanWed, 05 Mar 2008 12:58:32 GMTArgnekaRE: XML Workshop XII - Parsing a delimited stringhttp://www.sqlservercentral.com/Forums/Topic429626-356-1.aspxHi 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,AlanWed, 05 Mar 2008 11:55:44 GMTArgnekaRE: XML Workshop XII - Parsing a delimited stringhttp://www.sqlservercentral.com/Forums/Topic429626-356-1.aspxHi,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.Wed, 05 Mar 2008 10:59:12 GMTArgnekaRE: XML Workshop XII - Parsing a delimited stringhttp://www.sqlservercentral.com/Forums/Topic429626-356-1.aspxaplogies 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 elementsWed, 05 Mar 2008 07:46:47 GMTjacob sebastianRE: XML Workshop XII - Parsing a delimited stringhttp://www.sqlservercentral.com/Forums/Topic429626-356-1.aspxAre 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 &lt;= @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 ENDWed, 05 Mar 2008 07:45:37 GMTjacob sebastianRE: XML Workshop XII - Parsing a delimited stringhttp://www.sqlservercentral.com/Forums/Topic429626-356-1.aspxThank youWed, 05 Mar 2008 06:30:16 GMTArgnekaRE: XML Workshop XII - Parsing a delimited stringhttp://www.sqlservercentral.com/Forums/Topic429626-356-1.aspxHi,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,AlanWed, 05 Mar 2008 06:29:10 GMTArgnekaRE: XML Workshop XII - Parsing a delimited stringhttp://www.sqlservercentral.com/Forums/Topic429626-356-1.aspxYou 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.Tue, 04 Mar 2008 22:35:06 GMTjacob sebastianRE: XML Workshop XII - Parsing a delimited stringhttp://www.sqlservercentral.com/Forums/Topic429626-356-1.aspxHi,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&lt;@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 helpTue, 04 Mar 2008 11:48:49 GMTArgnekaRE: XML Workshop XII - Parsing a delimited stringhttp://www.sqlservercentral.com/Forums/Topic429626-356-1.aspxTo 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.htmlWed, 05 Dec 2007 10:28:07 GMTmojo-168709RE: XML Workshop XII - Parsing a delimited stringhttp://www.sqlservercentral.com/Forums/Topic429626-356-1.aspx[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. Wed, 05 Dec 2007 09:49:55 GMT Jack CorbettRE: XML Workshop XII - Parsing a delimited stringhttp://www.sqlservercentral.com/Forums/Topic429626-356-1.aspxI 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....Wed, 05 Dec 2007 09:23:23 GMTM GnatRE: XML Workshop XII - Parsing a delimited stringhttp://www.sqlservercentral.com/Forums/Topic429626-356-1.aspxI 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.Wed, 05 Dec 2007 06:17:08 GMT Jack CorbettRE: XML Workshop XII - Parsing a delimited stringhttp://www.sqlservercentral.com/Forums/Topic429626-356-1.aspxI 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.Wed, 05 Dec 2007 06:14:33 GMTjacob sebastianRE: XML Workshop XII - Parsing a delimited stringhttp://www.sqlservercentral.com/Forums/Topic429626-356-1.aspxThis 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.Wed, 05 Dec 2007 06:10:12 GMTLP-181697RE: XML Workshop XII - Parsing a delimited stringhttp://www.sqlservercentral.com/Forums/Topic429626-356-1.aspx[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.Wed, 05 Dec 2007 06:09:04 GMTJeff ModenRE: XML Workshop XII - Parsing a delimited stringhttp://www.sqlservercentral.com/Forums/Topic429626-356-1.aspxI understand the problem now. A schema will validate an entire &#100;ocument. 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).regardsJacobWed, 05 Dec 2007 03:00:39 GMTjacob sebastianRE: XML Workshop XII - Parsing a delimited stringhttp://www.sqlservercentral.com/Forums/Topic429626-356-1.aspxA 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.Wed, 05 Dec 2007 02:43:38 GMTPhil FactorRE: XML Workshop XII - Parsing a delimited stringhttp://www.sqlservercentral.com/Forums/Topic429626-356-1.aspxI 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? jacobWed, 05 Dec 2007 02:24:28 GMTjacob sebastianRE: XML Workshop XII - Parsing a delimited stringhttp://www.sqlservercentral.com/Forums/Topic429626-356-1.aspxI 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?Wed, 05 Dec 2007 02:16:34 GMTPhil FactorXML Workshop XII - Parsing a delimited stringhttp://www.sqlservercentral.com/Forums/Topic429626-356-1.aspxComments posted to this topic are about the item [B]<A HREF="/articles/XML/61618/">XML Workshop XII - Parsing a delimited string</A>[/B]Tue, 04 Dec 2007 22:29:51 GMTjacob sebastian