InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 02:51:14 GMT20http://www.sqlservercentral.com/Forums/Topic1404392-391-1.aspxHappy to be of service my friend.Tue, 15 Jan 2013 17:45:39 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1404392-391-1.aspxIt works! Thanks so much for sticking with me and solving this issue.The complete string is:[code="sql"]SELECT REPLACE(CAST(layerXMLData AS VARCHAR(MAX)), '/>', 'groupid=''' + CAST(groupid AS VARCHAR(5))+ ''' />')FROM [LOCAL_TEST].[dbo].[testtable10] [/code][quote]Are you saying this doesn't work?[code="sql"]SELECT REPLACE(layerXMLData, '/>', 'groupid=''' + CAST(groupid AS VARCHAR(5))+ '''/>')FROM [LOCAL_TEST].[dbo].[testtable10] [/code][/quote][/quote]I don't work much with TEXT columns because they're due to be deprecated in future SQL releases but you might try putting a cast around layerXMLData as:[code="sql"]CAST(layerXMLData AS VARCHAR(MAX))[/code][/quote]Tue, 15 Jan 2013 11:39:35 GMTjay.schwinnhttp://www.sqlservercentral.com/Forums/Topic1404392-391-1.aspx[quote][b]jay.schwinn (1/11/2013)[/b][hr]Dwain, It looks like my last post #1405712 was posted less than a minute after your new suggestion, so that post was actually a reply to your first comment, not your new suggestion.I tried your statement and got "Msg 8116, Level 16, State 1, Line 1Argument data type text is invalid for argument 1 of replace function."Thanks![quote]Are you saying this doesn't work?[code="sql"]SELECT REPLACE(layerXMLData, '/>', 'groupid=''' + CAST(groupid AS VARCHAR(5))+ '''/>')FROM [LOCAL_TEST].[dbo].[testtable10] [/code][/quote][/quote]I don't work much with TEXT columns because they're due to be deprecated in future SQL releases but you might try putting a cast around layerXMLData as:[code="sql"]CAST(layerXMLData AS VARCHAR(MAX))[/code]Fri, 11 Jan 2013 18:57:51 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1404392-391-1.aspxDwain, It looks like my last post #1405712 was posted less than a minute after your new suggestion, so that post was actually a reply to your first comment, not your new suggestion.I tried your statement and got "Msg 8116, Level 16, State 1, Line 1Argument data type text is invalid for argument 1 of replace function."Thanks![quote]Are you saying this doesn't work?[code="sql"]SELECT REPLACE(layerXMLData, '/>', 'groupid=''' + CAST(groupid AS VARCHAR(5))+ '''/>')FROM [LOCAL_TEST].[dbo].[testtable10] [/code][/quote]Fri, 11 Jan 2013 14:59:16 GMTjay.schwinnhttp://www.sqlservercentral.com/Forums/Topic1404392-391-1.aspx[quote][b]jay.schwinn (1/10/2013)[/b][hr]Dwain.c,Thank you for your input and advice. This is indeed how I would like the final output to look. However, I need the groupId to populate automatically based on the column value of groupIdFor instance, in this case, the groupId is indeed 206, but for the next record it could be 207, or 999, or anything. My desired results should be a little clearer from the previous post I made to Sean.[quote][b]dwain.c (1/8/2013)[/b][hr]How about something simple like this?[code="sql"]DECLARE @layerXMLData VARCHAR(8000) = '<polygon points=''-104.80,31.57 -104.80,31.59 -104.79,31.59 -104.79,31.57 '' id=''MT-209'' /> <polygon points=''-104.79,31.59 -104.79,31.60 -104.77,31.60 -104.77,31.59 '' id=''MT-209'' />'SELECT REPLACE(@layerXMLData, '/>', 'groupid=''206''/>')[/code][/quote][/quote]Are you saying this doesn't work?[code="sql"]SELECT REPLACE(layerXMLData, '/>', 'groupid=''' + CAST(groupid AS VARCHAR(5))+ '''/>')FROM [LOCAL_TEST].[dbo].[testtable10] [/code]Thu, 10 Jan 2013 17:55:17 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1404392-391-1.aspxDwain.c,Thank you for your input and advice. This is indeed how I would like the final output to look. However, I need the groupId to populate automatically based on the column value of groupIdFor instance, in this case, the groupId is indeed 206, but for the next record it could be 207, or 999, or anything. My desired results should be a little clearer from the previous post I made to Sean.[quote][b]dwain.c (1/8/2013)[/b][hr]How about something simple like this?[code="sql"]DECLARE @layerXMLData VARCHAR(8000) = '<polygon points=''-104.80,31.57 -104.80,31.59 -104.79,31.59 -104.79,31.57 '' id=''MT-209'' /> <polygon points=''-104.79,31.59 -104.79,31.60 -104.77,31.60 -104.77,31.59 '' id=''MT-209'' />'SELECT REPLACE(@layerXMLData, '/>', 'groupid=''206''/>')[/code][/quote]Thu, 10 Jan 2013 17:37:18 GMTjay.schwinnhttp://www.sqlservercentral.com/Forums/Topic1404392-391-1.aspxHave you tried the solution I suggested on your posted test data?All you need to do is change the hard-coded 206 in the replacement with:[code="sql"]CAST(groupid AS VARCHAR(5))[/code]and I think it will work.Thu, 10 Jan 2013 17:36:35 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1404392-391-1.aspxSean,Thanks for the pointers on correct posting. Apologies for my rookie mistake.I have been using and reviewing your how-to-post document and I've learned a lot just from reading it. I was stuck for a while after using the SELECT QUOTENAME to get my existing data into the statement. The INSERT INTO failed, and I finally figured out it was because of the apostrophes in my text field; I had to change them to double apostrophes. So, here is my statement, which I have successfully run:--===== If the test table already exists, drop it IF OBJECT_ID('[LOCAL_TEST].[dbo].[testtable10]','U') IS NOT NULL DROP TABLE [LOCAL_TEST].[dbo].[testtable10]--===== Create the test table with CREATE TABLE [LOCAL_TEST].[dbo].[testtable10] ( groupId INT IDENTITY PRIMARY KEY, layerXMLData TEXT )--===== All Inserts into the IDENTITY column SET IDENTITY_INSERT [LOCAL_TEST].[dbo].[testtable10] ON--===== Insert the test data into the test table INSERT INTO [LOCAL_TEST].[dbo].[testtable10] (groupId, layerXMLData)SELECT '206','<polygon points=''-104.09,31.79 -104.10,31.79 -104.10,31.80 -104.10,31.81 -104.0,31.8 '' id=''MT-207'' /><polygon points=''-104.07,31.81 -104.09,31.81 -104.091,31.82 -104.07,31.82 '' id=''MT-207'' />' UNION ALLSELECT '207','<polygon points=''-104.15,31.75 -104.15,31.75 -104.13,31.75 -104.13,31.75 '' id=''MT-208'' /><polygon points=''-104.15,31.75 -104.13,31.75 -104.13,31.74 -104.15,31.74 '' id=''MT-208'' /><polygon points=''-104.14,31.75 -104.14,31.75 -104.14,31.75 -104.14,31.75 '' id=''MT-208'' />' UNION ALLSELECT '208','<polygon points=''-104.80,31.57 -104.80,31.59 -104.79,31.59 -104.797,31.575 '' id=''MT-209'' /><polygon points=''-104.79,31.59 -104.79,31.60 -104.77,31.6 -104.77,31.59 '' id=''MT-209'' /><polygon points=''-104.79,31.56 -104.79,31.57 -104.77,31.57 -104.77,31.56 '' id=''MT-209'' /><polygon points=''-104.82,31.57 -104.81,31.57 -104.80,31.57 -104.80,31.56 -104.81,31.56 -104.82,31.56 '' id=''MT--100'' />'--===== Set the identity insert back to normal SET IDENTITY_INSERT [LOCAL_TEST].[dbo].[testtable10] OFFMy desired output would be to insert the label groupId and the value of groupId into the layerXMLData statement for every group of polygon points.e.g.:<polygon points='-104.09,31.79 -104.10,31.79 -104.10,31.80 -104.10,31.81 -104.0,31.8 ' id='MT-207' groupId='206' /><polygon points='-104.07,31.81 -104.09,31.81 -104.091,31.82 -104.07,31.82 ' id='MT-207' groupId='206' /><polygon points='-104.15,31.75 -104.15,31.75 -104.13,31.75 -104.13,31.75 ' id='MT-208' groupId='207' /><polygon points='-104.15,31.75 -104.13,31.75 -104.13,31.74 -104.15,31.74 ' id='MT-208' groupId='207' /><polygon points='-104.14,31.75 -104.14,31.75 -104.14,31.75 -104.14,31.75 ' id='MT-208' groupId='207' /><polygon points='-104.80,31.57 -104.80,31.59 -104.79,31.59 -104.797,31.575 ' id='MT-209' groupId='208' /><polygon points='-104.79,31.59 -104.79,31.60 -104.77,31.6 -104.77,31.59 ' id='MT-209' groupId='208' /><polygon points='-104.79,31.56 -104.79,31.57 -104.77,31.57 -104.77,31.56 ' id='MT-209' groupId='208' /><polygon points='-104.82,31.57 -104.81,31.57 -104.80,31.57 -104.80,31.56 -104.81,31.56 -104.82,31.56 ' id='MT--100' groupId='208' />Thanks for the training and for your time.[quote][b]Sean Lange (1/8/2013)[/b][hr]Hi and welcome to SSC. In order to provide any help you need to first provide enough details so we know where to begin. You will need to post ddl (create table statements), sample data (insert statements) and desired output based on your sample data. It takes a little more effort on your part but you will be rewarded with fast answers that are tested and accurate. Please see the first link in my signature for details about best practices when posting questions.[/quote]Thu, 10 Jan 2013 17:32:02 GMTjay.schwinnhttp://www.sqlservercentral.com/Forums/Topic1404392-391-1.aspxHow about something simple like this?[code="sql"]DECLARE @layerXMLData VARCHAR(8000) = '<polygon points=''-104.80,31.57 -104.80,31.59 -104.79,31.59 -104.79,31.57 '' id=''MT-209'' /> <polygon points=''-104.79,31.59 -104.79,31.60 -104.77,31.60 -104.77,31.59 '' id=''MT-209'' />'SELECT REPLACE(@layerXMLData, '/>', 'groupid=''206''/>')[/code]Tue, 08 Jan 2013 18:37:01 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1404392-391-1.aspxHi and welcome to SSC. In order to provide any help you need to first provide enough details so we know where to begin. You will need to post ddl (create table statements), sample data (insert statements) and desired output based on your sample data. It takes a little more effort on your part but you will be rewarded with fast answers that are tested and accurate. Please see the first link in my signature for details about best practices when posting questions.Tue, 08 Jan 2013 12:22:47 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1404392-391-1.aspxHello,This is my first post to this forum. Thanks in advance to anyone who takes the time to study this question. I am using Microsoft SQL Server Management Studio 2008.I want to run a query to insert a value from column groupId (data type int) into a string value for column layerXMLData (data type text). Because layerXMLData contains values for multiple polygon shapes, I'd like to be able to insert the groupId multiple times depending on the number of shapes. Also, since the data types are different, I'm betting on needing a CAST or CONVERT function in the statement.For a one row example:groupId = 206layerXMLData = <polygon points='-104.80,31.57 -104.80,31.59 -104.79,31.59 -104.79,31.57 ' id='MT-209' /><polygon points='-104.79,31.59 -104.79,31.60 -104.77,31.60 -104.77,31.59 ' id='MT-209' />In this case, there are two shapes, so I'd like to insert groupId as a value two times. The shapes are bound by the characters: < />(irrelevant to the question at hand, but FYI, I limited the decimal places for the points for simplicity's sake)The end result would look something like:layerXMLData = <polygon points='-104.80,31.57 -104.80,31.59 -104.79,31.59 -104.79,31.57 ' id='MT-209' groupid='206'/><polygon points='-104.79,31.59 -104.79,31.60 -104.77,31.60 -104.77,31.59 ' id='MT-209' groupid='206'/>Any pointers (or solutions(!)) out there for a rookie SQL user?Thanks!J.Tue, 08 Jan 2013 11:15:45 GMTjay.schwinn