How do I merge an XML blob with a comma delimited field value in a select statement?

Question

I have two pieces of data I need to merge. One field is an XML blob that holds URL's. The second field holds the label for those URL's. I need to display this in a web form, but cannot display a huge URL.

Field 1

<string>www.google.com</string>
<string>www.yahoo.com</string>

Field 2

Google,Yahoo

The result I need is to be able to generate an html link with the label in Field 2.

So the result I want is a string that looks like this

<a href="http://www.google.com">Google</a><br><a href="http://www.yahoo.com">Yahoo</a>

and so forth...

The example I have is something like this:

select  replace(cast(cast(replace(AnswerValue,  'xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.microsoft.com/2003/10/Serialization/Arrays"','') as xml).query('
                                for $string in //ArrayOfstring/string
                                return <a href="{data($string)}" target="_blank">TEST</a>       ') as nvarchar(max)), '</a>', '</a><br>') as AttachedDocuments

How can I achieve this? Is it possible?

Actually your last example seems exactly what you want, isn't it? — ergonaut, Sep 25 '15 at 16:35
So the 1st part is what I want, but I have another SQL column that has Google,Yahoo in that field. I want to put respectively Google into where it says TEST. — josh, Sep 25 '15 at 17:01
This may help: http://stackoverflow.com/questions/11018076/splitting-delimited-values-in-a-sql-column-into-multiple-rows — ergonaut, Sep 25 '15 at 17:11

Shnugo · Accepted Answer · 2015-09-26T20:47:40.793

Are you thinking of something like this?

EDIT: Just re-read your question and think I got this wrong... Maybe like this?

DECLARE @tbl TABLE(urls VARCHAR(MAX), names VARCHAR(MAX));
INSERT INTO @tbl VALUES
 ('<string>www.google.com</string><string>www.yahoo.com</string>','Google,Yahoo')
,('<string>www.test1.com</string><string>www.test2.com</string>','Test1,Test2');

SELECT  '<a href="http://' + vals.XmlUrls.value('*[1]','varchar(max)') + '">' + vals.XmlNames.value('*[1]','varchar(max)') + '</a>' 
       +'<br>'
       +'<a href="http://' + vals.XmlUrls.value('*[2]','varchar(max)') + '">' + vals.XmlNames.value('*[2]','varchar(max)') + '</a>'
FROM @tbl AS tbl
CROSS APPLY(SELECT CAST(urls AS XML) AS XmlUrls
                  ,CAST('<x>' + REPLACE(names,',','</x><x>') + '</x>' AS XML) AS XmlNames
            ) AS vals

Just paste it into an empty query window and execute. Adapt to your needs...

Approach before the Edit:

DECLARE @urls XML=
'<root>
<url>
<string>www.google.com</string>
<string>www.yahoo.com</string>
</url>
<url>
<string>www.test1.com</string>
<string>www.test2.com</string>
</url>
</root>';

SELECT '<a href="http://' + value1 + '">' +  name1 + '</a><br><a href="http://' + value2 + '">' + name2 + '</a>'
FROM @urls.nodes('/root/url') AS urls(url)
CROSS APPLY(SELECT urls.url.value('string[1]','varchar(max)') AS value1
                  ,urls.url.value('string[2]','varchar(max)') AS value2) AS resolved
CROSS APPLY
(
    SELECT CAST('<x>' + REPLACE(value1,'.','</x><x>') + '</x>' AS XML).value('x[2]','varchar(max)') AS name1
          ,CAST('<x>' + REPLACE(value2,'.','</x><x>') + '</x>' AS XML).value('x[2]','varchar(max)') AS name2
) AS names;

I will post what I came up with in another note, but you're pretty dead on. — josh, Sep 28 '15 at 17:39

How do I merge an XML blob with a comma delimited field value in a select statement?

1 Answers1