SQL Server: Selecting XML fields with HTML values

Question

I have a pre-built ecomm module for a website that is storing HTML in an XML field in SQL.

That being said when I select data from that field it's all jargon instead of my HTML.

How can I recode the XML into HTML in a select statement?

Data Stored in xml field [Overview]:

<locale en-US="&lt;h3&gt;As Shown Details&lt;/h3&gt; &lt;p&gt;6514/1 SN AWH TABLE LAMP AS SHOWN&lt;/p&gt; &lt;h3&gt;Item Details&lt;/h3&gt; &lt;div class=&quot;table-responsive&quot;&gt;  &lt;table class=&quot;table table-striped table-condensed&quot; width=&quot;100%&quot; border=&quot;0&quot;&gt;   &lt;tbody&gt;     &lt;tr&gt;       &lt;th scope=&quot;row&quot;&gt;Manufacturer&lt;/th&gt;       &lt;td&gt;Holtkotter International&lt;/td&gt;     &lt;/tr&gt;     &lt;tr&gt;       &lt;th scope=&quot;row&quot;&gt;Dimensions&lt;/th&gt;       &lt;td&gt;Width 7.25 x Depth 7.25 x Height 18.5&lt;/td&gt;     &lt;/tr&gt;     &lt;tr&gt;       &lt;th scope=&quot;row&quot;&gt;Seat Height&lt;/th&gt;       &lt;td&gt;&lt;/td&gt;     &lt;/tr&gt;     &lt;tr&gt;       &lt;th scope=&quot;row&quot;&gt;Arm Height&lt;/th&gt;       &lt;td&gt;&lt;/td&gt;     &lt;/tr&gt;     &lt;tr&gt;       &lt;th scope=&quot;row&quot;&gt;Inside Depth&lt;/th&gt;       &lt;td&gt;&lt;/td&gt;     &lt;/tr&gt;     &lt;tr&gt;       &lt;th scope=&quot;row&quot;&gt;Fabric Content&lt;/th&gt;       &lt;td&gt;&lt;/td&gt;     &lt;/tr&gt;     &lt;tr&gt;       &lt;th scope=&quot;row&quot;&gt;Country of Origin&lt;/th&gt;       &lt;td&gt;&lt;/td&gt;     &lt;/tr&gt;   &lt;/tbody&gt; &lt;/table&gt; &lt;/div&gt; &lt;div class=&quot;part1&quot;&gt;This modern table lamp adds style and versatility to virtually any decor. Equipped with a full-range, turn-knob dimmer and a 100 Watt Halogen bulb by Osram. Pair it with the matching wall sconce 9426, floor lamp 6515, or swing-arm floor lamp 9434.&lt;/div&gt;&lt;br&gt;&lt;br&gt;&lt;div class=&quot;part2&quot;&gt;Available in Hand Brushed Old Bronze (shown), Antique Brass, Brushed Brass, Chrome, and Satin Nickel Finishes.&lt;/div&gt;&lt;br&gt;&lt;br&gt;&lt;div class=&quot;part3&quot;&gt;Halogen Line Voltage 100W bulb included.&lt;/div&gt;" />

Should be clean HTML I've tried using

select convert(xml, [Overview]) as code
from [mytable]
for xml path (''), type

but it returns

<code><locale en-US="&lt;h3&gt;As Shown Details&lt;/h3&gt; &lt;p&gt;6514/1 SN AWH TABLE LAMP AS SHOWN&lt;/p&gt; &lt;h3&gt;Item Details&lt;/h3&gt; &lt;div class=&quot;table-responsive&quot;&gt;  &lt;table class=&quot;table table-striped table-condensed&quot; width=&quot;100%&quot; border=&quot;0&quot;&gt;   &lt;tbody&gt;     &lt;tr&gt;       &lt;th scope=&quot;row&quot;&gt;Manufacturer&lt;/th&gt;       &lt;td&gt;Holtkotter International&lt;/td&gt;     &lt;/tr&gt;     &lt;tr&gt;       &lt;th scope=&quot;row&quot;&gt;Dimensions&lt;/th&gt;       &lt;td&gt;Width 7.25 x Depth 7.25 x Height 18.5&lt;/td&gt;     &lt;/tr&gt;     &lt;tr&gt;       &lt;th scope=&quot;row&quot;&gt;Seat Height&lt;/th&gt;       &lt;td&gt;&lt;/td&gt;     &lt;/tr&gt;     &lt;tr&gt;       &lt;th scope=&quot;row&quot;&gt;Arm Height&lt;/th&gt;       &lt;td&gt;&lt;/td&gt;     &lt;/tr&gt;     &lt;tr&gt;       &lt;th scope=&quot;row&quot;&gt;Inside Depth&lt;/th&gt;       &lt;td&gt;&lt;/td&gt;     &lt;/tr&gt;     &lt;tr&gt;       &lt;th scope=&quot;row&quot;&gt;Fabric Content&lt;/th&gt;       &lt;td&gt;&lt;/td&gt;     &lt;/tr&gt;     &lt;tr&gt;       &lt;th scope=&quot;row&quot;&gt;Country of Origin&lt;/th&gt;       &lt;td&gt;&lt;/td&gt;     &lt;/tr&gt;   &lt;/tbody&gt; &lt;/table&gt; &lt;/div&gt; &lt;div class=&quot;part1&quot;&gt;This modern table lamp adds style and versatility to virtually any decor. Equipped with a full-range, turn-knob dimmer and a 100 Watt Halogen bulb by Osram. Pair it with the matching wall sconce 9426, floor lamp 6515, or swing-arm floor lamp 9434.&lt;/div&gt;&lt;br&gt;&lt;br&gt;&lt;div class=&quot;part2&quot;&gt;Available in Hand Brushed Old Bronze (shown), Antique Brass, Brushed Brass, Chrome, and Satin Nickel Finishes.&lt;/div&gt;&lt;br&gt;&lt;br&gt;&lt;div class=&quot;part3&quot;&gt;Halogen Line Voltage 100W bulb included.&lt;/div&gt;" /></code>

Perhaps some sample data and desired results would help

John Cappelletti
– John Cappelletti

2017-05-24 17:24:32 +00:00
Commented May 24, 2017 at 17:24 — John Cappelletti
– John Cappelletti, Commented May 24, 2017 at 17:24
Hi @JohnCappelletti i've just added some data

UserSN
– UserSN

2017-05-24 17:26:21 +00:00
Commented May 24, 2017 at 17:26 — UserSN
– UserSN, Commented May 24, 2017 at 17:26

Gottfried Lesigang · Accepted Answer · 2017-05-26 09:02:31Z

2

There's no need to do any manual re-codings. Reading the attribute value directly from the XML will do all the re-encoding for you implicitly:

DECLARE @mockup TABLE(Overview XML);
INSERT INTO @mockup(Overview) 
    VALUES(N'<locale en-US="&lt;h3&gt;As Shown Details&lt;/h3&gt; &lt;p&gt;6514/1 SN AWH TABLE LAMP AS SHOWN&lt;/p&gt; &lt;h3&gt;Item Details&lt;/h3&gt; &lt;div class=&quot;table-responsive&quot;&gt;  &lt;table class=&quot;table table-striped table-condensed&quot; width=&quot;100%&quot; border=&quot;0&quot;&gt;   &lt;tbody&gt;     &lt;tr&gt;       &lt;th scope=&quot;row&quot;&gt;Manufacturer&lt;/th&gt;       &lt;td&gt;Holtkotter International&lt;/td&gt;     &lt;/tr&gt;     &lt;tr&gt;       &lt;th scope=&quot;row&quot;&gt;Dimensions&lt;/th&gt;       &lt;td&gt;Width 7.25 x Depth 7.25 x Height 18.5&lt;/td&gt;     &lt;/tr&gt;     &lt;tr&gt;       &lt;th scope=&quot;row&quot;&gt;Seat Height&lt;/th&gt;       &lt;td&gt;&lt;/td&gt;     &lt;/tr&gt;     &lt;tr&gt;       &lt;th scope=&quot;row&quot;&gt;Arm Height&lt;/th&gt;       &lt;td&gt;&lt;/td&gt;     &lt;/tr&gt;     &lt;tr&gt;       &lt;th scope=&quot;row&quot;&gt;Inside Depth&lt;/th&gt;       &lt;td&gt;&lt;/td&gt;     &lt;/tr&gt;     &lt;tr&gt;       &lt;th scope=&quot;row&quot;&gt;Fabric Content&lt;/th&gt;       &lt;td&gt;&lt;/td&gt;     &lt;/tr&gt;     &lt;tr&gt;       &lt;th scope=&quot;row&quot;&gt;Country of Origin&lt;/th&gt;       &lt;td&gt;&lt;/td&gt;     &lt;/tr&gt;   &lt;/tbody&gt; &lt;/table&gt; &lt;/div&gt; &lt;div class=&quot;part1&quot;&gt;This modern table lamp adds style and versatility to virtually any decor. Equipped with a full-range, turn-knob dimmer and a 100 Watt Halogen bulb by Osram. Pair it with the matching wall sconce 9426, floor lamp 6515, or swing-arm floor lamp 9434.&lt;/div&gt;&lt;br&gt;&lt;br&gt;&lt;div class=&quot;part2&quot;&gt;Available in Hand Brushed Old Bronze (shown), Antique Brass, Brushed Brass, Chrome, and Satin Nickel Finishes.&lt;/div&gt;&lt;br&gt;&lt;br&gt;&lt;div class=&quot;part3&quot;&gt;Halogen Line Voltage 100W bulb included.&lt;/div&gt;" />')

SELECT m.Overview.value(N'(/locale/@en-US)[1]','nvarchar(max)')
FROM @mockup AS m

The result can not be casted to XML due to the less strict rules of HTML (namely the unclosed <br> elements). But it works perfectly. Embedded in a website the browser showed me this
(important Your CSS classes are missing of course...):

edited May 26, 2017 at 9:02

answered May 26, 2017 at 8:27

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Gottfried Lesigang Over a year ago

@JohnCappelletti One (related) hint. There's no need to re-encode entities with REPLACE. Try this:

DECLARE @s VARCHAR(100)='&lt;SomeEvilXml attr="This &amp;amp; that"/&gt;'; SELECT CAST('<x>' + @s + '</x>' AS XML).value(N'.','nvarchar(max)');

. The main advantage: If there is an unexpected entity, your REPLACE cascade will not catch it...

John Cappelletti Over a year ago

I see that now. My first attempt failed so I got bull-headed and forced it. I really should have taken a moment to consider it further. I would tell my kids (and staff), "There's no need to rush the wrong answer."

Collectives™ on Stack Overflow

SQL Server: Selecting XML fields with HTML values

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related