Bind xml column and regular columns from sql server in nested controls

Question

I have a stored procedure which returns a mixture of plain columns, and one 'column' of xml datatype. As in:

Select
     Field1,
     Field2,
    (
    SELECT
        Certification As '@certification',
        LCID As '@lcid'
    FROM
        dbo.MemberCertifications
    FOR XML PATH('certification'), TYPE, ROOT('certifications')
    ) AS Certifications 
 FROM
      .......

I'm binding this result to a gridview, and I need the xml column to be bound to a nested repeater as it contains parent-child data.

I've tried setting the repeater datasource to the column name, as in:

    <asp:Repeater ID="rp" runat="server" DataSource="<%# Eval("Certifications") %>">
      <ItemTemplate>                    
            <%#XPath("//@certification")%>                    
      </ItemTemplate>
    </asp:Repeater>   

but that doesn't work. It seems like it's coming across as a plain string. If I just write a line break inside the ItemTemplate it includes a break for each char in the column!

I've also tried using an xmldatasource inline, but I get a parser error:

    <asp:XmlDataSource ID="data_certs" runat="server" >
        <%# Eval("Certifications") %>
    </asp:XmlDataSource> 

I'm at the end of my rope here - I'm at the point where I'm just going to build up my result in code by deserializing the xml. I don't really need any gridview functionality anyway.

Any ideas?

Answer 1

Hey,

Can you use LINQ to XML? You could load it using something like:

XDocument document = XDocument.Load(" .. xml content here ..");
rpt.DataSource = document.Root.Elements("certification").Select(i => new { Field1 = i.Element("Field1").Value, Field2 = i.Element("Field2").Value });
rpt.DataBind();

Basically what I am doing is converting the XML document to an anonymous type, which the repeater can reflect against. I'm sure some of the syntax is a little off (don't remember if it's a Value property or somethign else, my linq to XML is a little rusty :-) ) but that's basically how it could be done from code.

Inline markup I don't know if any approach works since it comes from the database. XmlDataSource requires a file and not so much from a database (at least from what I know, could be wrong).

HTH.

Answer 2

While not exactly allowing me to do this completely declaratively, this works based on input from Brian. Perhaps the binding syntax is out there, but I don't have the time to sort it out!

Repeater:

<asp:Repeater ID="rp" runat="server" DataSource='<%# EnumerableCertifications(Eval("Certifications")) %>'>
  <ItemTemplate>                 
    <%#Eval("certification")%><br />
  </ItemTemplate>
</asp:Repeater>

Codebehind (vb.net, but you get the idea):

Public Function EnumerableCertifications(ByVal xml As Object) As IEnumerable
    If xml Is DBNull.Value OrElse String.IsNullOrEmpty(xml) Then
        Return Nothing
    Else
        Using sr As New System.IO.StringReader(xml.ToString())
            Dim document As XDocument = XDocument.Load(sr)
            Dim q = From c In document.Descendants("certification") Where c.Attribute("lcid").Value = Locale.ToString() _
                    Select New With {.certification = c.Attribute("certification").Value}
            Return q
        End Using
    End If

End Function