Xquery for inside for

Question

I'm trying to get directly html out from sql query (TSQL, sql server 2008 r2). I have following xml in one of the fields I would need to parse daily schedule by ul list with topic header listed only once, not for each subject. I'm not allowed to alter form of the xml data.

how should I alter this select clause to accomplish it?

select  Day1Schedule = T.Item.query
                                ('
                                    for $a in //Schedule/Day[@Number="1"]/Topic
                                    for $b in $a/Subject
                                               return 
                                        <ul>
                                        <li>
                                        <strong>
                                        {$a/Header/text()}
                                        </strong>
                                        </li>
                                        <li>
                                            {$b/text()}
                                        </li>
                                    </ul>
                                ')


<Schedule>
<Day Number="1">
    <Topic>
        <Header>Topic1</Header>
        <Subject>T1Subject1</Subject>
        <Subject>T1Subject2</Subject>
        <Subject>T1Subject3</Subject>
        <Subject>T1Subject4</Subject>
        <Subject>T1Subject5</Subject>
    </Topic>
    <Topic>
        <Header>Topic2</Header>
        <Subject>T2Subject1</Subject>
    </Topic>
    <Topic>
        <Header>Topic3</Header>
        <Subject>T3Subject1</Subject>
        <Subject>T3Subject2</Subject>
        <Subject>T3Subject3</Subject>
    </Topic>
</Day>
<Day Number="2">
    <Topic>
        <Header>Topic1</Header>
        <Subject>T1Subject1</Subject>
        <Subject>T1Subject2</Subject>
        <Subject>T1Subject3</Subject>
        <Subject>T1Subject4</Subject>
        <Subject>T1Subject5</Subject>
    </Topic>
    <Topic>
        <Header>Topic2</Header>
        <Subject>T2Subject1</Subject>
    </Topic>
    <Topic>
        <Header>Topic3</Header>
        <Subject>T3Subject1</Subject>
        <Subject>T3Subject2</Subject>
        <Subject>T3Subject3</Subject>
    </Topic>
</Day>
<Day Number="3">
    <Topic>
        <Header>Topic1</Header>
        <Subject>T1Subject1</Subject>
        <Subject>T1Subject2</Subject>
        <Subject>T1Subject3</Subject>
        <Subject>T1Subject4</Subject>
        <Subject>T1Subject5</Subject>
    </Topic>
    <Topic>
        <Header>Topic2</Header>
        <Subject>T2Subject1</Subject>
    </Topic>
    <Topic>
        <Header>Topic3</Header>
        <Subject>T3Subject1</Subject>
        <Subject>T3Subject2</Subject>
        <Subject>T3Subject3</Subject>
    </Topic>
</Day>
 </Schedule>

Thanks for any ideas?

Answer 1

Try something like this:

for $a in //Schedule/Day[@Number="1"]/Topic
return <ul>
           <li><strong>{$a/Header/text()}</strong></li>
           {for $b in $a/Subject
           return <li>{$b/text()}</li>}
       </ul>

It returns Header exactly once per Topic.