Problems With FOR XML AUTO

92 views Asked by At

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 ?

1

There are 1 answers

0
Mikael Eriksson On BEST ANSWER

Change XML PATH('') to XML PATH('tag')