for XML PATH

Steve Gray — Tue, 26 Aug 2008 22:22:00 GMT

One of the coolest things to come with SQL 2005 is the new 'for XML PATH' syntax. There are a lot of things that can be done with it, I'm going to demonstrate something moderately complex here. The task is to generate an XML file that has nested nodes. In other words, we want the following structure:

<Receivable documentNumber="CREDT000000000010" documentDate="08/26/2008">
  <Type>Credit Adjustment</Type>
  <Amount>300.00000</Amount>
  <Items>
    <Item type="Statement" itemNumber="346.001">
      <AppliedAmount>55.00000</AppliedAmount>
      <UnappliedAmount>0</UnappliedAmount>
    </Item>
    <Item type="Statement" itemNumber="346.002">
      <AppliedAmount>11.00000</AppliedAmount>
      <UnappliedAmount>0</UnappliedAmount>
    </Item>
  </Items>
</Receivable>

Note the nested 'Items' node. We achieve this using a nested SQL Select statement inside the main query, like this:

 select 
   'Credit Adjustment' as Type,
   ortrxamt as Amount,
   (
    select 
      case when apply.aptodcnm is null then 'Case' else 'Statement' end as <a href="mailto:'@type'">'@type'</a>,
      rtrim(isnull(apply.aptodcnm,'')) as <a href="mailto:'@itemNumber'">'@itemNumber'</a>,
      isnull(apply.apfrmaplyamt,0) as 'AppliedAmount',
      0 as 'UnappliedAmount'
     from RM20201 apply
     where  apply.Apfrdcnm = rm.docnumbr and rm.rmdtypal = apply.apfrdcty
     for xml path('Item') , type
   ) as 'Items'
  from RM20101 rm
  where rm.dex_row_id = 306
  for xml  path('Receivable')

The 'type' clause in the nested select tells the code to output XML

Linq to XML with ASP Gridview in VB

Crystal — Tue, 26 Aug 2008 15:35:00 GMT

XML FILE:

<title>Building</title>

<desc>As we enter into the construction phase of our building project, Pastor Campbell brings a series on building. Building not only walls and doors, but lives.</desc>

<author>Dr. Mark Campbell</author>

<date>20080824 Sun AM</date> <file>Building with Prayer</file>

Nehemiah 2:1-11

From old testament and new, Pastor Campbell continues the series on building. This week - building with prayer.

</description>

</details>

<file>Building with Prayer</file>

Nehemiah 1:1-11

As our new building is set to begin, Pastor Campbell begins a series in Nehemiah - God’s building story.

</description>

</details>

</series>

<desc>

Pastor Campbell continues our weekly bible study in Acts

</desc>

<author>Dr. Mark Campbell</author>

Pastor Campbell continues our weekly bible study in Acts

</description>

</details>

Acts 26:19-20

Pall continues his defense before King Agrippa

</description>

</details>

</series>

</seriesSermons>

Imports:

Imports System.Xml.Linq

Imports System.Linq

VB Code Behind: Page Load()

Dim strTitle As String = Request.QueryString("id")

Dim seriesXML As XDocument = XDocument.Load(Server.MapPath("/XML/seriesSermons.xml"))

Dim var = From series In seriesXML.Descendants("series") _

Where series.Elements("title").Value = strTitle

Dim det = From details In var.Descendants("details") _

Select strDate = details.Element("date").Value, _

file = details.Element("file").Value, _

desc = details.Element("description").Value()

gvSeriesDetail.DataSource = det.ToList()

gvSeriesDetail.DataBind()

VS Tools Forum

for XML PATH

Linq to XML with ASP Gridview in VB