I have a set of XML documents I am transforming and then inserting into a SQL 2005 db using OPENXML. I would like my transformation tighten up the relationships.

This is a tree fragment of what I have (if attributes are used, the source xml uses them; if elements are used, the source xml uses them):

<programs>
   <program>
    <particpants>
       <participant title="" first="Bob" last="Smith">
         <bio>Husband, Patient</bio>
       </participant>
       <participant title="" first="Jim" last="Wilson">
         <bio>MD</bio>
       </participant>
       <participant title="" first="Steve" last="Yzerman">
         <bio>Hall of Famer</bio>
       </participant>
     </participants>
     <video>
        <cValue>
          TPC_AR_ManagingSinusProblems_1
        </cValue>
        <title>
           Managing Sinus Problems - Overview
        </title>
        <shortttitle>
           Managing Sinus Problems - Overview
        </shortttitle>
        <VideoSpeedHIGH>
           ar_managing_sinus_problems_01_overview.wmv
        </VideoSpeedHIGH>
        <duration>
           00:06:43 (HH:MM:SS)
        </duration>
        <transcript>
        Anyone who has suffered a bad head cold....
      </transcript>
      </video>
      <video>
        <cValue>
           TPC_AR_ManagingSinusProblems_2
        </cValue>
        <title>
           Managing Sinus Problems - Cold Vs. Sinus
        </title>
        <shortttitle>
           Managing Sinus Problems - Cold Vs. Sinus
        </shortttitle>
        <VideoSpeedHIGH>
           ar_managing_sinus_problems_02_cold_vs_sinus.wmv
        </VideoSpeedHIGH>
        <duration>
           00:03:27 (HH:MM:SS)
        </duration>
        <transcript>
        Patients often wonder how long...
    </transcript>
      </video>
    </program>
  </programs>

So... in the source XML, the relationship between the participants is based on that the <videos> and <participants> are children of a program.

I would like the result document to include the set of Participants in each video element

<videos>
<video>
   <cValue>
      TPC_AR_ManagingSinusProblems_2
   </cValue>
   <title>
      Managing Sinus Problems - Cold Vs. Sinus
   </title>
   <shortttitle>
      Managing Sinus Problems - ColdVs.Sinus
   </shortttitle>
   <VideoSpeedHIGH>
      ar_managing_sinus_problems_02_cold_vs_sinus.wmv
   </VideoSpeedHIGH>
   <duration>00:03:27 (HH:MM:SS)</duration>
   <transcript>
        Patients often wonder how long...
   </transcript>
   <particpants>
      <participant>
         <title></title>
         <first></first>
         <middle></middle>
         <bio>Husband, Patient</bio>
       </participant>
       <participant>
         <title></title>
         <first></first>
         <middle></middle>
         <bio>Husband, Patient</bio>
       </participant>
   </participants>
</video>
</videos>








James Kitcher

Looks as if you're in essence just doing

<xsl:template match="video">
  <xsl:copy-of select="."/>
  <xsl:copy-of select="../participants"/>
</xsl:template>

except you're doing a little bit of transformation (attributes to elements) as well - but that doesn't affect the structure.

Michael Kay
http://www.saxonica.com/
Author, XSLT Programmer's Reference and XPath 2.0 Programmer's Reference

That worked like a charm! Thanks! I was really trying to make it more complicated that it was in my N00bish ways.

One other thing -- I'm often finding it troublesome to supress the default output. I tend to get everything or nothing at all. In this latest example, where I'm trying to walk the context down to the video element, I get nothing back.

<xsl:template match="/">
   <xsl:apply-templates />
</xsl:template>

<xsl:template match="*"/>

<xsl:template match="diseases">
   <xsl:apply-templates />
</xsl:template>

<xsl:template match="programs">
   <xsl:apply-templates />
</xsl:template>

<xsl:template match="program">
   <xsl:apply-templates />
</xsl:template>

<xsl:template match="video">
  <xsl:copy-of select="."/>
  <xsl:copy-of select="../participants"/>
</xsl:template>

What's the best approach to suppress the default output and cut to elements to be transformed?

James Kitcher