December 2, 2011 at 9:20 am
I have an image data type I would like to select into my XML document. When I select using query analyzer there is data there and formatted just as I would expect. However, when I put it into the FOR XML Path selection it comes up blank and the related element is not there at all. Can anyone tell me how to correct this? It is likely I need a slightly different FOR XML command but I can't figure out what I am missing!
select
opt.AnswerText as "answerOption/text",
opt.AnswerAbbr as "answerOption/abbr",
opt.AnswerValue as "answerOption/value",
opt.Ordinal as "answerOption/ordinal",
opt.RelatedImage as "answerOption/image/imgByte",
it.ColumnValue as "answerOption/image/imgType",
a.LongName as "attribute/longName",
a.ShortName as "attribute/shortName",
a.Information as "attribute/information"
from rcs.tblAttributeReview_Link ar
inner join rcs.tblAttribute a on ar.AttributeUID = a.AttributeUID
left join rcs.tblAnswerOption opt on a.AttributeUID = opt.AttributeUID
left join rcs.tblLookup it on opt.ImageType = it.LookupID FOR XML Path('reviewAttribute')
which produces:
<reviewAttribute>
<id>4</id>
<answerOption>
<id>8</id>
<text>I need an answer</text>
<value>0</value>
<ordinal>0</ordinal>
<enabled>0</enabled>
<image>
<imgByte></imgByte>
</image>
</answerOption>
<attribute>
<id>2EDD5095-550E-430E-B55C-1055D29364BB</id>
<longName>Attribute for Review</longName>
<shortName>Review</shortName>
<answerType>
<id>1</id>
<columnValue>Free Text</columnValue>
</answerType>
<class>
<id>14</id>
<columnValue>Response</columnValue>
</class>
<state>
<id>17</id>
<columnValue>Edit</columnValue>
</state>
<enabled>1</enabled>
<information>Some information that is interesting</information>
</attribute>
</reviewAttribute>
It would be perfect if only the imgByte data actually showed up! And there should be an imgType as well that exists when I select the data as data but is not showing up in the XML 🙁
In addition to the issue above I want to have the entire thing start with
<reviewAttribute xmlns="urn:reviewset-1.0">
I have found ways to state the namespace but the formatting is never right. If I try to add xmlns as an attribute to reviewAttribute SQL Server kicks an error that xmlns is not allowed as an attribute. If anyone knows how to get exactly this format as the namespace declaration I would really appreciate the pointer!
Thank you!
January 5, 2012 at 7:28 am
You can get the namespace by adding this with clause just before the query:
e.g.
with xmlnamespaces (default 'urn:reviewset-1.0')
select .....
Not sure why you are having problems with the image not coming through. As long as there is data, you should be able to store the image value in the xml. I've just done a quick test here and was able to create an xml document using FOR XML PATH and it had the encoded value from the image column.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply