Denormalized XML File

• ringovski

  Hall of Fame

  Points: 3521

  More actions

  August 13, 2014 at 8:44 pm

  #397199

  Hi All,

  This is my first attempt at creating a XSL from a XML file.

  I need to load the XML file data into a SQL table using a XSL file so the data is in a flat format. I am using VS2008 to load the XML and XSL to create a denormalized XML file. Which is then loaded using a XML source into a SQL DB.

  I have created a XSL file from my XML, but there appears to be something wrong with the code. As when I attempt to connect to the DB, zero columns appear from the denormalized file.

  A copy of the XML file structure is in attached screen shot batch.jpg.

  A copy of the zero columns is in attached screen shot columns.jpg

  My XSL code

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

  <xsl:output method="xml" encoding="UTF-8" indent="yes" />

  <xsl:template match="/">

  <xsl:for-each select="Export">

  <Batches>

  <xsl:for-each select="Export/Invoices">

  <Invoices>

  <xsl:for-each select="Export/Invoices/Invoice">

  <Invoice>

  <AgencyName></AgencyName>

  <TransDescription></TransDescription>

  <CampaignName></CampaignName>

  <AgencyNo></AgencyNo>

  <TransactionNo></TransactionNo>

  <CampaignNo></CampaignNo>

  <PurchaseOrderNo></PurchaseOrderNo>

  <CampaignAccountCode></CampaignAccountCode>

  <CampaignType></CampaignType>

  <InvoiceDate></InvoiceDate>

  <ExTaxTotal></ExTaxTotal>

  <IncTaxTotal></IncTaxTotal>

  <Agent></Agent>

  <AgentAccCode></AgentAccCode>

  <CoAgent></CoAgent>

  <CoAgentAccCode></CoAgentAccCode>

  <Items>

  <Item>

  <OrderItemNo></OrderItemNo>

  <Description></Description>

  <SaleCode></SaleCode>

  <SupplierNo></SupplierNo>

  <SupplierName></SupplierName>

  <PublicationName></PublicationName>

  <PublicationAdSizeDescription></PublicationAdSizeDescription>

  <PublicationAdSizeFixedX></PublicationAdSizeFixedX>

  <PublicationAdSizeFixedY></PublicationAdSizeFixedY>

  <ArtworkType></ArtworkType>

  <Date></Date>

  <Qty></Qty>

  <PriceExcGST></PriceExcGST>

  <PriceIncGST></PriceIncGST>

  <MediaRebateExcGST></MediaRebateExcGST>

  <MediaRebateIncGST></MediaRebateIncGST>

  </Item>

  </Items>

  </Invoice>

  </xsl:for-each><xsl:value-of select="." /><xsl:element name="br"/>

  <xsl:value-of select="." /><xsl:element name="br"/>

  </Invoices>

  </xsl:for-each><xsl:value-of select="." /><xsl:element name="br"/>

  </Batches>

  </xsl:for-each>

  </xsl:template>

  </xsl:stylesheet>

  If anyone can point out where the XSL code is wrong, thanks.:-D

• ringovski

  Hall of Fame

  Points: 3521

  More actions

  August 14, 2014 at 1:02 am

  #1737920

  ok i have created a new XSL file, but the resulting denormalised XML file header doesn't look right (see attached screen shot new.jpg)

  New XSL Code

  <?xml version='1.0' ?>

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

  <xsl:template match="/">

  <Export>

  <xsl:value-of select="Export"/>

  <xsl:attribute name="BatchNo">

  <xsl:value-of select="Export/@BatchNo"/>

  </xsl:attribute>

  <Invoices>

  <xsl:value-of select="Export/Invoices"/>

  <xsl:for-each select="Export/Invoices/Invoice">

  <Invoice>

  <AgencyName>

  <xsl:value-of select="AgencyName"/>

  </AgencyName>

  <TransDescription>

  <xsl:value-of select="TransDescription"/>

  </TransDescription>

  <CampaignName>

  <xsl:value-of select="CampaignName"/>

  </CampaignName>

  <AgencyNo>

  <xsl:value-of select="AgencyNo"/>

  </AgencyNo>

  <TransactionNo>

  <xsl:value-of select="TransactionNo"/>

  </TransactionNo>

  <CampaignNo>

  <xsl:value-of select="CampaignNo"/>

  </CampaignNo>

  <PurchaseOrderNo>

  <xsl:value-of select="PurchaseOrderNo"/>

  </PurchaseOrderNo>

  <CampaignAccountCode>

  <xsl:value-of select="CampaignAccountCode"/>

  </CampaignAccountCode>

  <CampaignType>

  <xsl:value-of select="CampaignType"/>

  </CampaignType>

  <InvoiceDate>

  <xsl:value-of select="InvoiceDate"/>

  </InvoiceDate>

  <ExTaxTotal>

  <xsl:value-of select="ExTaxTotal"/>

  </ExTaxTotal>

  <IncTaxTotal>

  <xsl:value-of select="IncTaxTotal"/>

  </IncTaxTotal>

  <Agent>

  <xsl:value-of select="Agent"/>

  </Agent>

  <AgentAccCode>

  <xsl:value-of select="AgentAccCode"/>

  </AgentAccCode>

  <CoAgent>

  <xsl:value-of select="CoAgent"/>

  </CoAgent>

  <CoAgentAccCode>

  <xsl:value-of select="CoAgentAccCode"/>

  </CoAgentAccCode>

  <Items>

  <xsl:value-of select="Items"/>

  <xsl:for-each select="Items/Item">

  <Item>

  <OrderItemNo>

  <xsl:value-of select="OrderItemNo"/>

  </OrderItemNo>

  <Description>

  <xsl:value-of select="Description"/>

  </Description>

  <SaleCode>

  <xsl:value-of select="SaleCode"/>

  </SaleCode>

  <SupplementalSaleCode>

  <xsl:value-of select="SupplementalSaleCode"/>

  </SupplementalSaleCode>

  <SupplierNo>

  <xsl:value-of select="SupplierNo"/>

  </SupplierNo>

  <SupplierName>

  <xsl:value-of select="SupplierName"/>

  </SupplierName>

  <PublicationName>

  <xsl:value-of select="PublicationName"/>

  </PublicationName>

  <PublicationAdSizeDescription>

  <xsl:value-of select="PublicationAdSizeDescription"/>

  </PublicationAdSizeDescription>

  <PublicationAdSizeFixedX>

  <xsl:value-of select="PublicationAdSizeFixedX"/>

  </PublicationAdSizeFixedX>

  <PublicationAdSizeFixedY>

  <xsl:value-of select="PublicationAdSizeFixedY"/>

  </PublicationAdSizeFixedY>

  <ArtworkType>

  <xsl:value-of select="ArtworkType"/>

  </ArtworkType>

  <Date>

  <xsl:value-of select="Date"/>

  </Date>

  <Qty>

  <xsl:value-of select="Qty"/>

  </Qty>

  <PriceExcGST>

  <xsl:value-of select="PriceExcGST"/>

  </PriceExcGST>

  <PriceIncGST>

  <xsl:value-of select="PriceIncGST"/>

  </PriceIncGST>

  <MediaRebateExcGST>

  <xsl:value-of select="MediaRebateExcGST"/>

  </MediaRebateExcGST>

  <MediaRebateIncGST>

  <xsl:value-of select="MediaRebateIncGST"/>

  </MediaRebateIncGST>

  </Item>

  </xsl:for-each>

  </Items>

  </Invoice>

  </xsl:for-each>

  </Invoices>

  </Export>

  </xsl:template>

  </xsl:stylesheet>

  Any ideas?:-)

• Eirikur Eiriksson

  SSC Guru

  Points: 182882

  More actions

  August 14, 2014 at 4:42 am

  #1737986

  Quick thought, add an output method element

  😎

  <xsl:output method="text" indent="no"/>

• ringovski

  Hall of Fame

  Points: 3521

  More actions

  August 18, 2014 at 6:07 pm

  #1739066

  I have altered the XSL code\file and have managed to get columns in 4 outputs, item, Items, invoice, invoices.

  <?xml version='1.0' ?>

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

  <xsl:template match="/">

  <Export>

  <xsl:attribute name="BatchNo">

  <xsl:value-of select="Export/@BatchNo"/>

  </xsl:attribute>

  <Invoices>

  <xsl:for-each select="Export/Invoices/Invoice">

  <Invoice>

  <AgencyName>

  <xsl:value-of select="AgencyName"/>

  </AgencyName>

  <TransDescription>

  <xsl:value-of select="TransDescription"/>

  </TransDescription>

  <CampaignName>

  <xsl:value-of select="CampaignName"/>

  </CampaignName>

  <AgencyNo>

  <xsl:value-of select="AgencyNo"/>

  </AgencyNo>

  <TransactionNo>

  <xsl:value-of select="TransactionNo"/>

  </TransactionNo>

  <CampaignNo>

  <xsl:value-of select="CampaignNo"/>

  </CampaignNo>

  <PurchaseOrderNo>

  <xsl:value-of select="PurchaseOrderNo"/>

  </PurchaseOrderNo>

  <CampaignAccountCode>

  <xsl:value-of select="CampaignAccountCode"/>

  </CampaignAccountCode>

  <CampaignType>

  <xsl:value-of select="CampaignType"/>

  </CampaignType>

  <InvoiceDate>

  <xsl:value-of select="InvoiceDate"/>

  </InvoiceDate>

  <ExTaxTotal>

  <xsl:value-of select="ExTaxTotal"/>

  </ExTaxTotal>

  <IncTaxTotal>

  <xsl:value-of select="IncTaxTotal"/>

  </IncTaxTotal>

  <Agent>

  <xsl:value-of select="Agent"/>

  </Agent>

  <AgentAccCode>

  <xsl:value-of select="AgentAccCode"/>

  </AgentAccCode>

  <CoAgent>

  <xsl:value-of select="CoAgent"/>

  </CoAgent>

  <CoAgentAccCode>

  <xsl:value-of select="CoAgentAccCode"/>

  </CoAgentAccCode>

  <Items>

  <xsl:for-each select="Items/Item">

  <Item>

  <OrderItemNo>

  <xsl:value-of select="OrderItemNo"/>

  </OrderItemNo>

  <Description>

  <xsl:value-of select="Description"/>

  </Description>

  <SaleCode>

  <xsl:value-of select="SaleCode"/>

  </SaleCode>

  <SupplementalSaleCode>

  <xsl:value-of select="SupplementalSaleCode"/>

  </SupplementalSaleCode>

  <SupplierNo>

  <xsl:value-of select="SupplierNo"/>

  </SupplierNo>

  <SupplierName>

  <xsl:value-of select="SupplierName"/>

  </SupplierName>

  <PublicationName>

  <xsl:value-of select="PublicationName"/>

  </PublicationName>

  <PublicationAdSizeDescription>

  <xsl:value-of select="PublicationAdSizeDescription"/>

  </PublicationAdSizeDescription>

  <PublicationAdSizeFixedX>

  <xsl:value-of select="PublicationAdSizeFixedX"/>

  </PublicationAdSizeFixedX>

  <PublicationAdSizeFixedY>

  <xsl:value-of select="PublicationAdSizeFixedY"/>

  </PublicationAdSizeFixedY>

  <ArtworkType>

  <xsl:value-of select="ArtworkType"/>

  </ArtworkType>

  <Date>

  <xsl:value-of select="Date"/>

  </Date>

  <Qty>

  <xsl:value-of select="Qty"/>

  </Qty>

  <PriceExcGST>

  <xsl:value-of select="PriceExcGST"/>

  </PriceExcGST>

  <PriceIncGST>

  <xsl:value-of select="PriceIncGST"/>

  </PriceIncGST>

  <MediaRebateExcGST>

  <xsl:value-of select="MediaRebateExcGST"/>

  </MediaRebateExcGST>

  <MediaRebateIncGST>

  <xsl:value-of select="MediaRebateIncGST"/>

  </MediaRebateIncGST>

  </Item>

  </xsl:for-each>

  </Items>

  </Invoice>

  </xsl:for-each>

  </Invoices>

  </Export>

  </xsl:template>

  </xsl:stylesheet>

  Though the objective is to try the four outputs in one, so they can be loaded into one SQL table.:-D

• Jeff Moden

  SSC Guru

  Points: 1003863

  More actions

  August 18, 2014 at 7:08 pm

  #1739072

  ringovski (8/18/2014)

  ---

  I have altered the XSL code\file and have managed to get columns in 4 outputs, item, Items, invoice, invoices.

  ...{snip}...

  Though the objective is to try the four outputs in one, so they can be loaded into one SQL table.:-D

  Then why not just create a nice, simple, TSV file and call it a day? 😉

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• ringovski

  Hall of Fame

  Points: 3521

  More actions

  August 19, 2014 at 12:06 am

  #1739081

  Thanks for the reply, how do you create a TSV file from a XML\XSLT file?

• Eirikur Eiriksson

  SSC Guru

  Points: 182882

  More actions

  August 19, 2014 at 12:34 am

  #1739088

  ringovski (8/19/2014)

  ---

  Thanks for the reply, how do you create a TSV file from a XML\XSLT file?

  TSV stands for Tab Separated Values, since your incoming data is in XML format I don't think it is applicable. In my opinion, a simpler approach would be to use XQuery to denormalize the incoming data.

  😎