XML Transform Consolidating multiple nodes and a foreign key

Question

I'm having issues transforming this XML for import into Access. I need to be have everything in the FA, Prospect and shipping information node to fall under one Request with the OrderID as the primary key. However I would like to have Program Selection and Marketing Materials in separate tables, with the OrderID as the foreign key. The User (FA) makes one request (identified by the OrderID) for one prospect, however each request can have multiple Program Selections and Marketing Material requests.

Here is the XML I'm working with:

<?xml version="1.0" encoding="UTF-8"?>
<Requests>
  <Request>
    <Request_Type><![CDATA[Wealth Onshore]]></Request_Type>
    <Employee_ID><![CDATA[334668]]></Employee_ID>
    <FA>
      <Division><![CDATA[IFS]]></Division>
      <Name>
        <Advisor_firstname><![CDATA[Steven]]></Advisor_firstname>
        <Advisor_lastname><![CDATA[Doe]]></Advisor_lastname>
      </Name>
      <Address>
        <Advisor_Address><![CDATA[9999 Ross Ave]]></Advisor_Address>
        <Advisor_Address2><![CDATA[suite 5200]]></Advisor_Address2>
        <Advisor_Address3><![CDATA[null]]></Advisor_Address3>
        <Advisor_Address4 />
        <Advisor_City><![CDATA[New York]]></Advisor_City>
        <Advisor_State><![CDATA[TX]]></Advisor_State>
        <Advisor_Zip><![CDATA[99999]]></Advisor_Zip>
        <Advisor_Country />
      </Address>
      <Company_Name><![CDATA[Tyco]]></Company_Name>
      <Advisor_Phone><![CDATA[(333) 721-6457]]></Advisor_Phone>
      <Advisor_Email><![CDATA[jon@company.com]]></Advisor_Email>
      <Advisor_Business_Name><![CDATA[null]]></Advisor_Business_Name>
      <Manager_Name><![CDATA[Bill]]></Manager_Name>
    </FA>
    <Prospect>
      <Subscription_Type><![CDATA[Existing Investor]]></Subscription_Type>
      <Prospect_Firstname><![CDATA[A big trust]]></Prospect_Firstname>
      <Prospect_Lastname />
      <Address>
        <Prospect_Address><![CDATA[900 Street]]></Prospect_Address>
        <Prospect_Address2><![CDATA[null]]></Prospect_Address2>
        <Prospect_Address3><![CDATA[null]]></Prospect_Address3>
        <Prospect_City><![CDATA[Great FALLS]]></Prospect_City>
        <Prospect_State><![CDATA[TX]]></Prospect_State>
        <Prospect_Zip><![CDATA[99999]]></Prospect_Zip>
        <Prospect_Country />
      </Address>
      <Prospect_Investor_Type><![CDATA[Family Entity]]></Prospect_Investor_Type>
      <Prospect_Employee_Investor><![CDATA[N]]></Prospect_Employee_Investor>
    </Prospect>
    <ProgramSelection>
      <Interest><![CDATA[I Interest]]></Interest>
      <Program><![CDATA[Hedge Funds & Private Capital (Monthly Investment) Managed Futures & Commodities Real Estate]]></Program>
      <Number_Kits><![CDATA[0]]></Number_Kits>
      <Type_Kit />
    </ProgramSelection>
    <MarketingMaterial>
      <ExecutiveSummary />
      <FactSheet>
        <Program><![CDATA[Equity Hedge Legends]]></Program>
        <Program><![CDATA[Pinehurst]]></Program>
      </FactSheet>
      <ProductProfile>
        <Program><![CDATA[Equity Hedge Legends]]></Program>
        <Program><![CDATA[Pinehurst]]></Program>
      </ProductProfile>
      <Presentation>
        <Program><![CDATA[Equity Hedge Legends]]></Program>
        <Program><![CDATA[Pinehurst]]></Program>
      </Presentation>
      <Overview />
    </MarketingMaterial>
    <ShippingInformation>
      <Shipping_Method><![CDATA[Online Delivery]]></Shipping_Method>
      <CO_SUB_RC><![CDATA[0079486]]></CO_SUB_RC>
      <Ship_To><![CDATA[Online Delivery to Advisor]]></Ship_To>
      <Other_Name><![CDATA[null]]></Other_Name>
      <Other_Address><![CDATA[null]]></Other_Address>
      <Other_Address2><![CDATA[null]]></Other_Address2>
      <Other_City><![CDATA[null]]></Other_City>
      <Other_State><![CDATA[null]]></Other_State>
      <Other_Zip><![CDATA[null]]></Other_Zip>
      <Other_Country />
      <Prospect_Email />
      <Special_Instructions><![CDATA[null]]></Special_Instructions>
    </ShippingInformation>
    <OrderID><![CDATA[82281]]></OrderID>
    <OrderDateTime><![CDATA[10/20/2015 15:17:04]]></OrderDateTime>
    <OrderComments><![CDATA[null]]></OrderComments>
    <ApprovalStatus><![CDATA[Approved]]></ApprovalStatus>
  </Request>
</Requests>

And here is the transforming I'm using, or trying to use...

    <xsl:stylesheet version="1.0"      xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:output indent="yes"/>


    <xsl:template match="@*|node()">
    <xsl:copy>
        <xsl:apply-templates select="@*|node()"/>
    </xsl:copy>
    </xsl:template>

    <xsl:template match="Name">
    <Name>
        <OrderID><xsl:value-of select="../OrderID"/></OrderID>
        <xsl:apply-templates select="@*|node()"/>
    </Name>
    </xsl:template>
    <xsl:template match="Address">
    <Address>
        <OrderID><xsl:value-of select="../OrderID"/></OrderID>
        <xsl:apply-templates select="@*|node()"/>
    </Address>
    </xsl:template>
    <xsl:template match="FA">
    <FA>
        <OrderID><xsl:value-of select="../OrderID"/></OrderID>
        <xsl:apply-templates select="@*|node()"/>
    </FA>
    </xsl:template>
    <xsl:template match="Prospect">
    <Prospect>
        <OrderID><xsl:value-of select="../OrderID"/></OrderID>
        <xsl:apply-templates select="@*|node()"/>
    </Prospect>
    </xsl:template>
    <xsl:template match="ProgramSelection">
    <ProgramSelection>
        <OrderID><xsl:value-of select="../OrderID"/></OrderID>
        <xsl:apply-templates select="@*|node()"/>
    </ProgramSelection>
    </xsl:template>
    <xsl:template match="FactSheet">
    <FactSheet>
        <OrderID><xsl:value-of select="../../OrderID"/></OrderID>
        <xsl:apply-templates select="@*|node()"/>
    </FactSheet>
    </xsl:template>
    <xsl:template match="ProductProfile">
    <ProductProfile>
        <OrderID><xsl:value-of select="../../OrderID"/></OrderID>
        <xsl:apply-templates select="@*|node()"/>
    </ProductProfile>
 </xsl:template>
    <xsl:template match="Presentation">
    <Presentation>
        <OrderID><xsl:value-of select="../../OrderID"/></OrderID>
        <xsl:apply-templates select="@*|node()"/>
    </Presentation>
    </xsl:template>
    <xsl:template match="MarketingMaterial">
    <MarketingMaterial>
        <OrderID><xsl:value-of select="../OrderID"/></OrderID>
        <xsl:apply-templates select="@*|node()"/>
    </MarketingMaterial>
    </xsl:template>
    <xsl:template match="ShippingInformation">
    <ShippingInformation>
        <OrderID><xsl:value-of select="../OrderID"/></OrderID>
        <xsl:apply-templates select="@*|node()"/>
    </ShippingInformation>
    </xsl:template>
    <xsl:template match="ExecutiveSummary">
    <ExecutiveSummary>
        <OrderID><xsl:value-of select="../../OrderID"/></OrderID>
        <xsl:apply-templates select="@*|node()"/>
    </ExecutiveSummary>
</xsl:template>


    </xsl:stylesheet>

Edit: the result I seeking for the Marketing Materials and Program Selection nodes would be something like this.

    <ProgramSelection>
        <Selection>
            <Interest>I Interest</Interest>
            <Program>FundChoice1</Program>
            <OrderID>19827</OrderiD>
        </Selection>
        <Selection>
            <Interest>I Interest</Interest>
            <Program>Fund Choice2</Program>
            <OrderID>19827</OrderiD>
        </Selection>    
    </ProgramSelection>
    <FactSheets>
        <Sheet>
            <Program>Equity Hedge Legends</Program>
            <OrderID>82281</OrderID>
        </Sheet>
        <Sheet>
            <Program>Pinehurst</Program>
            <OrderID>82281</OrderID>
        </Sheet>
    </FactSheet>
    <ProductProfile>
        <Profile>
            <Program>Pinehurst</Program>
            <OrderID>82281</OrderID>
        </Profile>
        <Profile>
            <Program>Equity Hedge Legends</Program>
            <OrderID>82281</OrderID>
            </Profile>
    </ProductProfile>
  <Presentations>
        <Presentation>
            <Program>Equity Hedge Legends</Program>
            <OrderID>82281</OrderID>
        </Presentation>
        <Presentation>
            <Program>Pinehurst</Program>
            <OrderID>82281</OrderID>
        </Presentation>
  </Presentations>

and please forgive my formatting, I'm extremely new to XML and XSLT. Thank you!!

Could you also post the expected result of the transformation? — michael.hor257k, Oct 21 '15 at 17:51
Please post actual XML, not that copy/pasted stuff from Internet Explorer. Also, what's the point of populating literally every element with an OrderID that can easily be read from a defined spot in the document anyway? — Tomalak, Oct 21 '15 at 17:58
Expected results posted. Thanks for your input, I'm new to XML. — Chris, Oct 21 '15 at 18:42
When you say "this didn't work" do you mean the xslt in the answer below? If so, you should have added a comment to the answer (not the question) so the answerer would be notified. — Gord Thompson, Oct 21 '15 at 23:19
By the way, your desired output may not import adequately as nested nodes still appear. If using wizard, Access may prompt you on import to select which nest to choose and if done with code using `Application.ImportXML`, several tables may import together. — Parfait, Oct 22 '15 at 00:12
@Chris Please post your expected result as **code**, not as a description. — michael.hor257k, Oct 22 '15 at 13:07
@Michael.hor257k I would if I knew how to write code...I just lost my developer and I'm in over my head with XML/XSLT — Chris, Oct 22 '15 at 13:17

score 1 · Answer 1 · answered Oct 21 '15 at 19:20

1

Try something like :

XSLT 1.0

<xsl:stylesheet version="1.0" 
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>
<xsl:strip-space elements="*"/>

<xsl:variable name="orderID" select="/Requests/Request/OrderID" />

<!-- identity transform -->
<xsl:template match="@*|node()">
    <xsl:copy>
        <xsl:apply-templates select="@*|node()"/>
    </xsl:copy>
</xsl:template>

<xsl:template match="FA | Prospect | ProgramSelection | FactSheet">
    <xsl:copy>
        <xsl:apply-templates select="@*|node()"/>
        <OrderID><xsl:value-of select="$orderID"/></OrderID>
    </xsl:copy>
</xsl:template>

</xsl:stylesheet>

answered Oct 21 '15 at 19:20

michael.hor257k

113,275
6
33
51

Thank you for trying, but this didn't work. It added the OrderID field, but didn't repeat the OrderID values – Chris Oct 22 '15 at 12:43
That is a great tool! The XML result does look correct, however when I import it into Access, things go awry. For instance, in the Fact Sheet table it shows "Pinehurst" in the Program field, and "82281" in the OrderID field. But that's the only one it shows, however per the XML results it should also have "Equity hedge Legends" and "82281" (I think...) Thanks! – Chris Oct 22 '15 at 13:20
@Chris You need to find out what Access can import before you can ask how to get XSLT to produce it. – michael.hor257k Oct 22 '15 at 18:47
I assembled some test data using the layout I included in my last edit, and it worked exactly how I need it. Thank you! – Chris Oct 22 '15 at 19:03
@Chris it would be helpful if you could show an input that matches the output example - as the mapping is not quite clear. Note also that the output you show is not well-formed XML (no single root element). – michael.hor257k Oct 22 '15 at 19:29

score 0 · Answer 2 · edited May 23 '17 at 12:03

Consider the following XSLT using your original posted XML data. You will need to iterate off each child program using xsl:for-each.

XSLT

<xsl:transform xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
<xsl:output version="1.0" encoding="UTF-8" indent="yes" />

<xsl:template match="Request">
<xsl:variable name="frnkey" select="OrderID"/>

 <Requests>  
  <ProgramSelection>
    <xsl:for-each select="ProgramSelection">
      <Selection>        
        <xsl:copy-of select="Interest"/>
        <xsl:copy-of select="Program"/>
        <OrderID><xsl:value-of select="$frnkey"/></OrderID>
      </Selection>
    </xsl:for-each>
  </ProgramSelection>

  <FactSheet>
    <xsl:for-each select="MarketingMaterial/FactSheet/Program">
     <Sheet>      
      <xsl:copy-of select="."/>
      <OrderID><xsl:value-of select="$frnkey"/></OrderID>
     </Sheet>
    </xsl:for-each>
  </FactSheet>

  <ProductProfile>
    <xsl:for-each select="MarketingMaterial/ProductProfile/Program">
     <Profile>      
      <xsl:copy-of select="."/>
      <OrderID><xsl:value-of select="$frnkey"/></OrderID>
     </Profile>
    </xsl:for-each>
  </ProductProfile>

  <Presentations>
    <xsl:for-each select="MarketingMaterial/Presentation/Program">
     <Presentation>      
      <xsl:copy-of select="."/>
      <OrderID><xsl:value-of select="$frnkey"/></OrderID>
     </Presentation>
    </xsl:for-each>
  </Presentations>

 </Requests>

</xsl:template>
</xsl:transform>

OUTPUT

<?xml version="1.0" encoding="UTF-8"?>
<Requests>
  <ProgramSelection>
    <Selection>
      <Interest>I Interest</Interest>
      <Program>Hedge Funds &amp; Private Capital (Monthly Investment) Managed Futures &amp; Commodities Real Estate</Program>
      <OrderID>82281</OrderID>
    </Selection>
  </ProgramSelection>
  <FactSheet>
    <Sheet>
      <Program>Equity Hedge Legends</Program>
      <OrderID>82281</OrderID>
    </Sheet>
    <Sheet>
      <Program>Pinehurst</Program>
      <OrderID>82281</OrderID>
    </Sheet>
  </FactSheet>
  <ProductProfile>
    <Profile>
      <Program>Equity Hedge Legends</Program>
      <OrderID>82281</OrderID>
    </Profile>
    <Profile>
      <Program>Pinehurst</Program>
      <OrderID>82281</OrderID>
    </Profile>
  </ProductProfile>
  <Presentations>
    <Presentation>
      <Program>Equity Hedge Legends</Program>
      <OrderID>82281</OrderID>
    </Presentation>
    <Presentation>
      <Program>Pinehurst</Program>
      <OrderID>82281</OrderID>
    </Presentation>
  </Presentations>
</Requests>

Aside - usually the XSLT community admonishes to avoid the for-each looping (which reminds me of the debate in the R community to use the apply family instead of for loop). However, both agree for-loop is not intrinsically evil only when done excessively. For purposes of the MS Access import where additional nodes must be created per child item for the row and column two-dimensional structure it may be warranted here.

XML Transform Consolidating multiple nodes and a foreign key

2 Answers2