XML explicit, execute scalar and the xpath
I meet this scenario that you want to put your html tag on database.the field type is varchar.and then you want to wrap it with CDATA , so it's not treated as the xml node.
and You want to put a root tag on it. then xml explicit comes in handy for this.
select 1 as Tag,
0 as parent,
id as [Answer!1!id],
label as [Answer!1!label],
htmlpart as [Answer!1!!CDATA]
from answer_swe
where publish=1
for xml explicit, ROOT('Answers')
You need to set the tag and parent, And then after that you can select your 'column as [tagname!attributename] . If there is no name on it will treated as Element.
Meaning it will append it as child not attribute. and then the ROOT comes in play for the root xml. The end result is <Answers><Answer id="value" label="value"><!CDATA[[ your html tag ]]</Answer><Answers>
and then on your javascript or serverside. When you select nodes The xpath is //Answer/text()
as for the Cross Browser Value Text of xml nodes is xmlNodes%5Bn%5D.nodeValue
One thing that is weirdo. is if you use execute scalar. it will truncate the result. it's because the xml will be appended as per segment. So to over come this Make a variable XML on your store procedure.
then select that variable as the return value.
The execute scalar won't truncate it.
So , the Correct Store procedure is:
declare @xml XML
set @xml=(select 1 as Tag,
0 as parent,
id as [Answer!1!id],
label as [Answer!1!label],
htmlpart as [Answer!1!!CDATA]
from answer_swe
where publish=1
for xml explicit, ROOT('Answers'))
select @xml