August 13, 2014 at 8:44 pm
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
August 14, 2014 at 1:02 am
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?:-)
August 14, 2014 at 4:42 am
Quick thought, add an output method element
😎
<xsl:output method="text" indent="no"/>
August 18, 2014 at 6:07 pm
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
August 18, 2014 at 7:08 pm
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
Change is inevitable... Change for the better is not.
August 19, 2014 at 12:06 am
Thanks for the reply, how do you create a TSV file from a XML\XSLT file?
August 19, 2014 at 12:34 am
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.
😎
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply