troubleshooting Question

Joining split records with XML problem

Avatar of MariaHalt
MariaHaltFlag for United States of America asked on
Microsoft SQL ServerMicrosoft DevelopmentMicrosoft SQL Server 2008
6 Comments1 Solution265 ViewsLast Modified:
Let's pretend we have the following table.  And I want to join the SplitValue records into one record based on Id.  The following FOR XML works, but where I have html tags in the SplitValue, the SplitValue is converted or something when joined up...grrr...I know I need to convert something to xml, but where, what, when?  I tried a bunch of things and am quickly subcumbing to late afternoon scatterbraindom.

CREATE TABLE tempdb.dbo.mh_split (
Id SMALLINT NOT NULL,
Idx SMALLINT NOT NULL,
SplitValue VARCHAR(255) NULL)

INSERT INTO  tempdb.dbo.mh_split VALUES (1, 0, '["CreateDate":20130101')
INSERT INTO  tempdb.dbo.mh_split VALUES (1, 1, '"FirstName":Marcia')
INSERT INTO  tempdb.dbo.mh_split VALUES (1, 2, '"LastName":Brady')
INSERT INTO  tempdb.dbo.mh_split VALUES (1, 3, '"Title":<p>Oldest Sister</p>]')

INSERT INTO  tempdb.dbo.mh_split VALUES (2, 0, '["CreateDate":20130101')
INSERT INTO  tempdb.dbo.mh_split VALUES (2, 1, '"FirstName":Jan')
INSERT INTO  tempdb.dbo.mh_split VALUES (2, 2, '"LastName":Brady')
INSERT INTO  tempdb.dbo.mh_split VALUES (2, 3, '"Title":<p>Middle Sister</p>]')

INSERT INTO  tempdb.dbo.mh_split VALUES (3, 0, '["CreateDate":20130101')
INSERT INTO  tempdb.dbo.mh_split VALUES (3, 1, '"FirstName":Cindy')
INSERT INTO  tempdb.dbo.mh_split VALUES (3, 2, '"LastName":Brady')
INSERT INTO  tempdb.dbo.mh_split VALUES (3, 3, '"Title":<p>Youngest Sister</p>]')

SELECT OUTSIDE.Id,
REPLACE(
(SELECT CONVERT(VARCHAR(255), INSIDE.SplitValue) + ','
FROM tempdb.dbo.mh_split INSIDE
WHERE OUTSIDE.Id = INSIDE.Id
FOR XML PATH ('')
) + '$', ',$', '') AS [JoinedValue]      
FROM tempdb.dbo.mh_split OUTSIDE
GROUP BY OUTSIDE.Id
ORDER BY OUTSIDE.Id

--Getting these results
["CreateDate":20130101,"FirstName":Marcia,"LastName":Brady,"Title":&lt;p&gt;Oldest Sister&lt;/p&gt;]
["CreateDate":20130101,"FirstName":Jan,"LastName":Brady,"Title":&lt;p&gt;Middle Sister&lt;/p&gt;]
["CreateDate":20130101,"FirstName":Cindy,"LastName":Brady,"Title":&lt;p&gt;Youngest Sister&lt;/p&gt;]

--Want these results
["CreateDate":20130101,"FirstName":Marcia,"LastName":Brady,"Title":<p>Oldest Sister</p>]
["CreateDate":20130101,"FirstName":Jan,"LastName":Brady,"Title":<p>Middle Sister&lt</p>]
["CreateDate":20130101,"FirstName":Cindy,"LastName":Brady,"Title":<p>Youngest Sister</p>]


--DROP TABLE tempdb.dbo.mh_split
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 6 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros