Using FOR XML EXPLICIT

I need to generate xml for a process that will serialize an object.  The form of the xml must take this shape:


- <CriteriaList>
- <Criteria xsi:type="DateCriteria">
  <DataType>datetime</DataType>
  <CriteriaName>Date Criteria 1</CriteriaName>
  <WhereTableName>trans</WhereTableName>
  <WhereColumnName>colXXXX</WhereColumnName>
- <SqlFilter xsi:type="BetweenSqlFilter">
  <Negate>true</Negate>
  </SqlFilter>
- <SelectedKeys>
  <anyType xsi:type="xsd:dateTime">2008-01-24T16:48:28.3659643-08:00</anyType>
  <anyType xsi:type="xsd:dateTime">2008-02-23T16:48:28.3669643-08:00</anyType>
  </SelectedKeys>
  <Negate>true</Negate>
  <Advanced>false</Advanced>
  <IncludeInWhere>true</IncludeInWhere>
  <StartDate>2008-01-24T16:48:28.3659643-08:00</StartDate>
  <EndDate>2008-02-23T16:48:28.3669643-08:00</EndDate>
  </Criteria>

I am stuck on trying to produce this part of the structure

<SqlFilter xsi:type="BetweenSqlFilter">
  <Negate>true</Negate>
  </SqlFilter>
- <SelectedKeys>

using this statement below all the SqlFilter stuff gets stuck on at the end of the last Criteria -  I need SQLFilter to be a at the same level as Criteria BUT also be able to use the "type" directive.

select
            1                              tag,
            null                        parent,
            c.CriteriaTypeKey                         [Criteria!1!type],
            d.ReportCriteriaTypeID      [Criteria!1!DataType!element],
            c.CriteriaName             [Criteria!1!CriteriaName!element],
            c.WhereTable             [Criteria!1!WhereTableName!element],
            c.WhereColumn            [Criteria!1!WhereColumnName!element],
            null                         [SqlFilter!2!type],
            null                         [SqlFilter!2!Negate!element]

        from Report a
        join ReportReportCriteria b on a.ReportKey = b.ParentReportKey
        join ReportCriteria c on b.ReportCriteriaKey = b.ReportCriteriaKey
        join ReportCriteriaType d on c.CriteriaTypeKey  = d.ReportCriteriaType
      where a.ReportKey = @ReportKey
      union
      select
            2                              tag,
            1                              parent,
            null,
            null,
            null,
            null,
            null,
            null,
            null
        from Report a
        join ReportReportCriteria b on a.ReportKey = b.ParentReportKey
        join ReportCriteria c on b.ReportCriteriaKey = b.ReportCriteriaKey
        join ReportCriteriaType d on c.CriteriaTypeKey  = d.ReportCriteriaType      
       where a.ReportKey = @ReportKey
      for XML explicit



DylanJones1Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Anthony PerkinsConnect With a Mentor Commented:
Since there are 3 levels you are probably going to need three UNION'd statements.
0
All Courses

From novice to tech pro — start learning today.