Denormalized XML File
-
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
-
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?:-)
-
Quick thought, add an output method element
<xsl:output method="text" indent="no"/> -
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
-
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) -
Thanks for the reply, how do you create a TSV file from a XML\XSLT file?
-
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 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply