i have the following Query:
SELECT QuestionID_PK ,
QuestionTitle ,
( SELECT dbo.Tags.TagID_PK ,
dbo.Tags.TagTitle ,
dbo.Tags.TagTitle_EN
FROM dbo.Question_Tag_Rel
INNER JOIN dbo.Questions
ON dbo.Question_Tag_Rel.QuestionID_FK = dbo.Questions.QuestionID_PK
INNER JOIN dbo.Tags
ON dbo.Question_Tag_Rel.TagID_FK = dbo.Tags.TagID_PK
AND dbo.Questions.QuestionID_PK = '2116'
FOR
XML PATH('') ,
TYPE ,
ELEMENTS
) AS Tags
FROM Questions
WHERE QuestionID_PK = '2116'
FOR XML AUTO ,
ELEMENTS;
which produces this xml:
<Questions>
<QuestionID_PK>2116</QuestionID_PK>
<QuestionTitle>Trying to find the execution time of my code using this</QuestionTitle>
<Tags>
<TagID_PK>3</TagID_PK>
<TagTitle>جافا</TagTitle>
<TagTitle_EN>Java</TagTitle_EN>
<TagID_PK>8</TagID_PK>
<TagTitle>بايثون</TagTitle>
<TagTitle_EN>Python</TagTitle_EN>
<TagID_PK>9</TagID_PK>
<TagTitle>أندرويد</TagTitle>
<TagTitle_EN>Android</TagTitle_EN>
</Tags>
</Questions>
as you can see, i want (TagID_PK,TagTitle,TagTitle_EN)tags to be inside parent XML tag ,here is a sample of what the query should output:
<Questions>
<QuestionID_PK>2116</QuestionID_PK>
<QuestionTitle>Trying to find the execution time of my code using this</QuestionTitle>
<Tags>
<tag>
<TagID_PK>3</TagID_PK>
<TagTitle>جافا</TagTitle>
<TagTitle_EN>Java</TagTitle_EN>
</tag>
<tag>
<TagID_PK>8</TagID_PK>
<TagTitle>بايثون</TagTitle>
<TagTitle_EN>Python</TagTitle_EN>
</tag>
<tag>
<TagID_PK>9</TagID_PK>
<TagTitle>أندرويد</TagTitle>
<TagTitle_EN>Android</TagTitle_EN>
</tag>
</Tags>
</Questions>
in order to bind it to my Repeater in my ASP.NET Application,
Can you please help me with this ?
Change
XML PATH('')
toXML PATH('tag')