none
Polling two database Tables and map to a common xml structure RRS feed

  • Question

  • Hi everybody,

    I'm working on a common task - and don't find the solution. So I really need your help:

    Given two tables in the Database (Order,Orderposition) I can poll this data by SQL and receive a flat XML Structure:

    Select Order.ID, Position.ArticleNumber from Order left join Position on Order.ID = Position.FK_OrderID;
    

    Here is the resulting XML:
    <POLLINGSTMTRECORD>
    	<ID>1</ID>
    	<ArticleNumber>ABC</ArticleNumber>
    </POLLINGSTMTRECORD>
    <POLLINGSTMTRECORD>
    	<ID>1</ID>
    	<ArticleNumber>DEF</ArticleNumber>
    </POLLINGSTMTRECORD>
    <POLLINGSTMTRECORD>
    	<ID>1</ID>
    	<ArticleNumber>GHI</ArticleNumber>
    </POLLINGSTMTRECORD>
    <POLLINGSTMTRECORD>
    	<ID>2</ID>
    	<ArticleNumber>DEF</ArticleNumber>
    </POLLINGSTMTRECORD>
    <POLLINGSTMTRECORD>
    	<ID>2</ID>
    	<ArticleNumber>GHI</ArticleNumber>
    </POLLINGSTMTRECORD>
    <POLLINGSTMTRECORD>
    	<ID>3</ID>
    	<ArticleNumber>XYZ</ArticleNumber>
    </POLLINGSTMTRECORD>
    

    Now I want to map this to a "normalized" format like this:

    <Orders>
    	<Order>
    		<ID>1</ID>
    		<Positions>
    			<ArticleNumber>ABC</ArticleNumber>
    			<ArticleNumber>DEF</ArticleNumber>
    			<ArticleNumber>GHI</ArticleNumber>
    		</Positions>	
    	</Order>
    	<Order>
    		<ID>2</ID>
    		<Positions>
    			<ArticleNumber>DEF</ArticleNumber>
    			<ArticleNumber>GHI</ArticleNumber>
    		</Positions>	
    	</Order>
    	<Order>
    		<ID>3</ID>
    		<Positions>
    			<ArticleNumber>XYZ</ArticleNumber>
    		</Positions>	
    	</Order>
    </Orders>
    

    Is there a smart way to do the mapping?

    Thursday, December 29, 2011 3:30 PM

Answers

  • Hi,
    You should use Muenchian grouping for this to work.
    Below is an example, with the assumption the resulting xml is missing the rootnode, I presumed 'POLLINGSTMTRECORDS' as its rootnode:
    <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt" xmlns:var="http://schemas.microsoft.com/BizTalk/2003/var" exclude-result-prefixes="msxsl var" version="1.0">
    	<xsl:output omit-xml-declaration="yes" indent="yes" method="xml" version="1.0"/>
    	<xsl:key name="group" match="POLLINGSTMTRECORD" use="ID"/>
    	<xsl:template match="POLLINGSTMTRECORDS">
    		<Orders>
    			<xsl:for-each select="POLLINGSTMTRECORD[count(. | key('group', ID)[1]) = 1]">
    				<Order>
    					<ID>
    						<xsl:value-of select="ID"/>
    					</ID>
    					<Positions>
    						<xsl:for-each select="key('group', ID)">
    							<ArticleNumber>
    								<xsl:value-of select="ArticleNumber"/>
    							</ArticleNumber>
    						</xsl:for-each>
    					</Positions>
    				</Order>
    			</xsl:for-each>
    		</Orders>
    	</xsl:template>
    </xsl:stylesheet>
    
    

    Regards,
    René
    Monday, January 2, 2012 11:07 AM