How do I Concatenate it. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How do I Concatenate it.

Author

Topic

SCHEMA
Posting Yak Master

192 Posts

Posted - 2009-07-29 : 11:07:14

Col1( int) Col2 (Varchar(max)
1 <item id="215F12DB-4B34-4A88-8EDE-8EAB5DE95DDC" attachmentId="B1FEF81E-2D91-469C-B603-0CA18DCC6759" displayValue="Other Attachments" type="DOCUMENT" purposeCode="1" resourceTypeCode="1" requiredFlag="-1" maxAttachments="1" minAttachments="1" useCustomAttachmentPage="-1" currentlyUsed="-1"> <action id="A0DF8C0D-7988-4AB0-9428-C81CC8101D7C" displayValue="View" /> </item>

1 <item id="D1684267-4786-4C61-ABAE-1302732E4682" attachmentId="F4A488C5-2369-4127-BC0D-17058F809ADD" displayValue="Other Attachments" type="DOCUMENT" purposeCode="1" resourceTypeCode="1" requiredFlag="-1" maxAttachments="1" minAttachments="1" useCustomAttachmentPage="-1" currentlyUsed="-1"> <action id="CC8B1B9A-974A-4D38-946D-A58ACFA00F8F" displayValue="View" /> </item>

How Do I concatenate into 1 row?

I created UDF but How I can i generate newid() (Unique) for it?

X002548
Not Just a Number

15586 Posts

Posted - 2009-07-29 : 11:21:42

SELECT CONVERT(varchar(25),Col1)+Col2

????

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-07-29 : 11:30:32

Or maybe you need this?

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

SCHEMA
Posting Yak Master

192 Posts

Posted - 2009-07-29 : 11:33:52

I have done concatenation with UDF but problem is I can't create function with newid().
I need to create newid() for each itemid and actionid . How Do I do it?

jeremygiaco
Starting Member

14 Posts

Posted - 2009-07-29 : 11:41:41

Your question isnt very clear, but this is how you would parse/select the xml and add the 1 as the first column...if you want to create a newid() then just replace "id" in the select statement with NewId():

Declare @iDoc int
Declare @XMLString xml
Declare @XMLRoot varchar(max)
Set @XMLString = '<item id="215F12DB-4B34-4A88-8EDE-8EAB5DE95DDC"
attachmentId="B1FEF81E-2D91-469C-B603-0CA18DCC6759"
displayValue="Other Attachments"
type="DOCUMENT"
purposeCode="1"
resourceTypeCode="1"
requiredFlag="-1"
maxAttachments="1"
minAttachments="1"
useCustomAttachmentPage="-1"
currentlyUsed="-1">
<action id="A0DF8C0D-7988-4AB0-9428-C81CC8101D7C" displayValue="View" />
</item>'
Exec sp_xml_preparedocument @iDoc OUTPUT, @XMLString

Select 1,
id,
attachmentId,
displayValue,
type,
purposeCode,
resourceTypeCode,
requiredFlag,
maxAttachments,
minAttachments,
useCustomAttachmentPage,
currentlyUsed,
actionid,
actiondisplayValue
From OpenXML(@idoc,'/item')
With (
id uniqueidentifier,
attachmentId uniqueidentifier,
displayValue varchar(50),
type varchar(50),
purposeCode int,
resourceTypeCode int,
requiredFlag int,
maxAttachments int,
minAttachments int,
useCustomAttachmentPage int,
currentlyUsed int,
actionid uniqueidentifier '/@action',
actiondisplayValue varchar(50) '/@action'
)
Exec sp_xml_removedocument @iDoc

Jeremy Giaco

SCHEMA
Posting Yak Master

192 Posts

Posted - 2009-07-29 : 12:12:04

I just need to Concatenate in 1 row. Itemid and ActionId should have newid()unique everytime.

jeremygiaco
Starting Member

14 Posts

Posted - 2009-07-29 : 12:41:11

You could try something like this for the concatination, if the length of the guid can change for some reason, then you can just modify accordingly for getting the charindex of the second quote after you find the first one..it sounds like you want to add a newid without adding a new column, if you just want to put both rows in a single column, then you can use my post from 11:47...

/* Creating this table for working purposes only, assuming you
are working with a table, the concatination would be the same
if you were working with just variables or something.
*/
Create Table #Temp(Col1 int, Col2 varchar(max))

Insert Into #Temp
Select 1, '<item id="215F12DB-4B34-4A88-8EDE-8EAB5DE95DDC" attachmentId="B1FEF81E-2D91-469C-B603-0CA18DCC6759" displayValue="Other Attachments" type="DOCUMENT" purposeCode="1" resourceTypeCode="1" requiredFlag="-1" maxAttachments="1" minAttachments="1" useCustomAttachmentPage="-1" currentlyUsed="-1"> <action id="A0DF8C0D-7988-4AB0-9428-C81CC8101D7C" displayValue="View" /> </item>' union
Select 1, '<item id="D1684267-4786-4C61-ABAE-1302732E4682" attachmentId="F4A488C5-2369-4127-BC0D-17058F809ADD" displayValue="Other Attachments" type="DOCUMENT" purposeCode="1" resourceTypeCode="1" requiredFlag="-1" maxAttachments="1" minAttachments="1" useCustomAttachmentPage="-1" currentlyUsed="-1"> <action id="CC8B1B9A-974A-4D38-946D-A58ACFA00F8F" displayValue="View" /> </item>'

Select Replace(
Replace(Col2,Substring(Col2,Charindex('item id="',Col2) + 9,36),NewId()),
Substring(Col2,Charindex('action id="',Col2) + 11,36),NewId())
From #Temp

Drop Table #Temp

Jeremy Giaco

jeremygiaco
Starting Member

14 Posts

Posted - 2009-07-29 : 12:47:24

Since im still slightly unclear, might as well give one more solution..

if you want to replace the old item and actionid's with newid's AND you want to select all the rows from a table into a single xml string...Then you can do this..

/* Creating this table for working purposes only, assuming you
already have a table with multiple rows, and you need to
make a single row
*/
Create Table #Temp(Col1 int, Col2 varchar(max))

Insert Into #Temp
Select 1, '<item id="215F12DB-4B34-4A88-8EDE-8EAB5DE95DDC" attachmentId="B1FEF81E-2D91-469C-B603-0CA18DCC6759" displayValue="Other Attachments" type="DOCUMENT" purposeCode="1" resourceTypeCode="1" requiredFlag="-1" maxAttachments="1" minAttachments="1" useCustomAttachmentPage="-1" currentlyUsed="-1"> <action id="A0DF8C0D-7988-4AB0-9428-C81CC8101D7C" displayValue="View" /> </item>' union
Select 1, '<item id="D1684267-4786-4C61-ABAE-1302732E4682" attachmentId="F4A488C5-2369-4127-BC0D-17058F809ADD" displayValue="Other Attachments" type="DOCUMENT" purposeCode="1" resourceTypeCode="1" requiredFlag="-1" maxAttachments="1" minAttachments="1" useCustomAttachmentPage="-1" currentlyUsed="-1"> <action id="CC8B1B9A-974A-4D38-946D-A58ACFA00F8F" displayValue="View" /> </item>'

Declare @XML varchar(max)
Set @XML = '<root>'
Select @XML = @XML + Replace(
Replace(Col2,Substring(Col2,Charindex('item id="',Col2) + 9,36),NewId()),
Substring(Col2,Charindex('action id="',Col2) + 11,36),NewId())
From #Temp

Select @XML + '</root>'

Drop Table #Temp

Jeremy Giaco

SCHEMA
Posting Yak Master

192 Posts

Posted - 2009-07-29 : 12:55:43

This didn't work.

While concatenating, itemid and actionid for both rows are getting same newid().How do I pass unique for each itemid and actionid.

jeremygiaco
Starting Member

14 Posts

Posted - 2009-07-29 : 14:27:37

quote:
Originally posted by SCHEMA

This didn't work.

While concatenating, itemid and actionid for both rows are getting same newid().How do I pass unique for each itemid and actionid.

Yeah, thats rough, unless you passed in two parameters, but even then each row will have the same parameter as the row before (though the itemid and actionid would be different. If I were you i would research doing this without a udf..will probably be more efficient anyways...something like this perhaps:

--Assuming this data is already stored in a table...
Create Table #RealTable(Col1 int, Col2 varchar(max))

Insert Into #RealTable
Select 1, '<item id="215F12DB-4B34-4A88-8EDE-8EAB5DE95DDC" attachmentId="B1FEF81E-2D91-469C-B603-0CA18DCC6759" displayValue="Other Attachments" type="DOCUMENT" purposeCode="1" resourceTypeCode="1" requiredFlag="-1" maxAttachments="1" minAttachments="1" useCustomAttachmentPage="-1" currentlyUsed="-1"> <action id="A0DF8C0D-7988-4AB0-9428-C81CC8101D7C" displayValue="View" /> </item>' union
Select 1, '<item id="D1684267-4786-4C61-ABAE-1302732E4682" attachmentId="F4A488C5-2369-4127-BC0D-17058F809ADD" displayValue="Other Attachments" type="DOCUMENT" purposeCode="1" resourceTypeCode="1" requiredFlag="-1" maxAttachments="1" minAttachments="1" useCustomAttachmentPage="-1" currentlyUsed="-1"> <action id="CC8B1B9A-974A-4D38-946D-A58ACFA00F8F" displayValue="View" /> </item>' union
Select 1, '<item id="EC52A279-082D-431D-8FEF-60D9D0062D6D" attachmentId="B1FEF81E-2D91-469C-B603-0CA18DCC6759" displayValue="Other Attachments" type="DOCUMENT" purposeCode="1" resourceTypeCode="1" requiredFlag="-1" maxAttachments="1" minAttachments="1" useCustomAttachmentPage="-1" currentlyUsed="-1"> <action id="C39E897E-D179-49B2-B2CF-6C41B95F9A47" displayValue="View" /> </item>' union
Select 1, '<item id="E04D7BAA-E530-4E33-B5F7-702F14E7FE9A" attachmentId="F4A488C5-2369-4127-BC0D-17058F809ADD" displayValue="Other Attachments" type="DOCUMENT" purposeCode="1" resourceTypeCode="1" requiredFlag="-1" maxAttachments="1" minAttachments="1" useCustomAttachmentPage="-1" currentlyUsed="-1"> <action id="FFF0520B-977E-42D4-85E1-31293134E39D" displayValue="View" /> </item>'

--Dont use UDF if at all possible
--Stage data in a temp table or update data in table if possible...
Select Col1,Replace(
Replace(Col2,Substring(Col2,Charindex('item id="',Col2) + 9,36),NewId()),
Substring(Col2,Charindex('action id="',Col2) + 11,36),NewId()) Col2
Into #TempTable
From #RealTable

--Concat data into single variable
Declare @XML varchar(max)
SET @XML = '<root>'
Select @XML = @XML + Col2
From #TempTable
SET @XML = @XML + '</root>'

--Here is your data with unique guids for itemid and actionid per row
Select @XML

--You wouldnt actually drop your real table
Drop Table #RealTable
--You would actually drop your temp table
Drop Table #TempTable

Jeremy Giaco

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-29 : 16:27:51

1. Create a View with this definition "CREATE VIEW dbo.vwMyGUID AS SELECT NEWID()"
2. Now you can use this view in your function to create a GUID.

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources