Link to home
Start Free TrialLog in
Avatar of Mani Pazhana
Mani PazhanaFlag for United States of America

asked on

XSLT - XML to CSV

I am doing XML to CSV file conversion.

i want
WOCreateDate and CodingDate1  - will always appear in the output as mandatory field with null value only. (no other values will be accepted.)

-------------------------------------------------

XSLT:

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:csv="csv:csv"
xmlns:msxsl="urn:schemas-microsoft-com:xslt" exclude-result-prefixes="msxsl">
      
      <xsl:output method="text" encoding="iso-8859-1"/>

      <xsl:key name="kInvoiceDetailId" match="/Invoice/InvoiceLineItemDetail" use="concat(../VendorInvoiceNumber,'-',OriginatingInvoiceLineId)"/>

      
      <!-- DL being used (Comma in this case) -->
      <xsl:param name="DL" select="'&#44;'"/>
      <!-- Checking for carriage return -->
      <xsl:param name="CR" select="'&#xA;'"/>

      <xsl:strip-space elements="*"/>

      <!-- Column Headers -->
      <!--Create header row of element names
            (All fields are sent to JDE that are in the CIM, single line per invoice details)-->
      
      <xsl:variable name="headers" >
            <headers>ClientId</headers> <!-- 1 -->
            <headers>OriginatingInvoiceId</headers> <!-- 2 -->
            <headers>VendorId</headers> <!-- 3 -->
            <headers>AlternativeVendorId</headers> <!-- 4 -->
            <headers>VendorInvoiceNumber</headers> <!-- 5 -->
            <headers>InvoiceDate</headers> <!-- 6 -->
            <headers>InvoiceReceiveDate</headers> <!-- 7 -->
            <headers>GLDate</headers> <!-- 8 -->
            <headers>Remark</headers> <!-- 9 -->
            <headers>TotalInvoiceAmount</headers> <!-- 10 -->
            <headers>CurrencyCode</headers> <!-- 11 -->
            <headers>TaxRateAreaCode</headers> <!-- 12 -->
            <headers>StatusCodeCode</headers> <!-- 13 -->
            <headers>StatusCodeDescription</headers> <!-- 14 -->
            <headers>ClientApprovalNumber</headers> <!-- 15 -->
            <headers>AuthorizedBy</headers> <!-- 16 -->
            <headers>BatchReferenceNumber</headers> <!-- 17 -->
            <headers>InvoiceSource</headers> <!-- 18 -->
            <headers>NumberOfLines</headers> <!-- 19 -->
            <headers>OriginatingInvoiceLineId</headers> <!-- 20 -->
            <headers>POLineNumber</headers> <!-- 21 -->
            <headers>LineItemDescription</headers> <!-- 22 -->
            <headers>LocationId</headers> <!-- 23 -->
            <headers>BuildingName</headers> <!-- 24 -->
            <headers>LineItemAmount</headers> <!-- 25 -->
            <headers>TaxableLineItemAmount</headers> <!-- 26 -->
            <headers>TaxAmountLineItem</headers> <!-- 27 -->
            <headers>CompanyId</headers> <!-- 28 -->
            <headers>GLAccountCode</headers> <!-- 29 -->
            <headers>Subsidiary</headers> <!-- 30 -->
            <headers>SubLedger</headers> <!-- 31 -->
            <headers>SubLedgerType</headers> <!-- 32 -->
            <headers>LedgerType</headers> <!-- 33 -->
            <headers>CostCenter</headers> <!-- 34 -->
            <headers>AccountBlock</headers> <!-- 35 -->
            <headers>GLAccountDescription</headers> <!-- 36 -->
            <headers>ClientAccountCode</headers> <!-- 37 -->
            <headers>WBSCode</headers> <!-- 38 -->
            <headers>OriginatingProjectNumber</headers> <!-- 39 -->
            <headers>OriginatingProjectSystem</headers> <!-- 40 -->
            <headers>ClientPONumber</headers> <!-- 41 -->
            <headers>WorkOrderNumber</headers> <!-- 42 -->
            <headers>WorkOrderCompletedDate</headers> <!-- 43 -->
            <headers>WorkOrderCreatedDate</headers> <!-- 44 -->
            <headers>CodingBlock1</headers> <!-- 45 -->
            <headers>CodingBlock2</headers> <!-- 46 -->
            <headers>CodingBlock3</headers> <!-- 47 -->
            <headers>CodingBlock4</headers> <!-- 48 -->
            <headers>CodingBlock5</headers> <!-- 49 -->
            <headers>CodingBlock6</headers> <!-- 50 -->
            <headers>CodingDate1</headers> <!-- 51 -->
            <headers>CodingDate2</headers> <!-- 52 -->
      </xsl:variable>      

      <xsl:variable name="columns" >
            <columns>ClientId</columns> <!-- 1 -->
            <columns>OriginatingInvoiceId</columns> <!-- 2 -->
            <columns>VendorId</columns> <!-- 3 -->
            <columns>AlternativeVendorId</columns> <!-- 4 -->
            <columns>VendorInvoiceNumber</columns> <!-- 5 -->
            <columns>InvoiceDate</columns> <!-- 6 -->
            <columns>InvoiceReceiveDate</columns> <!-- 7 -->
            <columns>GLDate</columns> <!-- 8 -->
            <columns>Remark</columns> <!-- 9 -->
            <columns>TotalInvoiceAmount</columns> <!-- 10 -->
            <columns>CurrencyCode</columns> <!-- 11 -->
            <columns>TaxRateAreaCode</columns> <!-- 12-->
            <columns>common:Code</columns> <!-- 13 -->
            <columns>common:Description</columns> <!-- 14 -->
            <columns>ClientApprovalNumber</columns> <!-- 15 -->
            <columns>AuthorizedBy</columns> <!-- 16 -->
            <columns>BatchReferenceNumber</columns> <!-- 17 -->
            <columns>InvoiceSource</columns> <!-- 18 -->
            <columns>NumberOfLines</columns> <!-- 19 -->
            <columns>OriginatingInvoiceLineId</columns> <!-- 20 -->
            <columns>POLineNumber</columns> <!-- 21 -->
            <columns>LineItemDescription</columns> <!-- 22 -->
            <columns>LocationId</columns> <!-- 23 -->
            <columns>BuildingName</columns> <!-- 24 -->
            <columns>LineItemAmount</columns> <!-- 25 -->
            <columns>TaxableLineItemAmount</columns> <!-- 26 -->
            <columns>TaxAmountLineItem</columns> <!-- 27 -->
            <columns>CompanyId</columns> <!-- 28 -->
            <columns>GLAccountCode</columns> <!-- 29 -->
            <columns>Subsidiary</columns> <!-- 30 -->
            <columns>SubLedger</columns> <!-- 31 -->
            <columns>SubLedgerType</columns> <!-- 32 -->
            <columns>LedgerType</columns> <!-- 33 -->
            <columns>CostCenter</columns> <!-- 34 -->
            <columns>AccountBlock</columns> <!-- 35 -->
            <columns>GLAccountDescription</columns> <!-- 36 -->
            <columns>ClientAccountCode</columns> <!-- 37 -->
            <columns>WBSCode</columns> <!-- 38 -->
            <columns>OriginatingProjectNumber</columns> <!-- 39 -->
            <columns>OriginatingProjectSystem</columns> <!-- 40 -->
            <columns>ClientPONumber</columns> <!-- 41 -->
            <columns>WorkOrderNumber</columns> <!-- 42 -->
            <columns>WorkOrderCompletedDate</columns> <!-- 43 -->
            <columns>WorkOrderCreatedDate</columns> <!-- 44 -->
            <columns>CodingBlock1</columns> <!-- 45 -->
            <columns>CodingBlock2</columns> <!-- 46 -->
            <columns>CodingBlock3</columns> <!-- 47 -->
            <columns>CodingBlock4</columns> <!-- 48 -->
            <columns>CodingBlock5</columns> <!-- 49 -->
            <columns>CodingBlock6</columns> <!-- 50 -->
            <columns>CodingDate1</columns> <!-- 51 -->
            <columns>CodingDate2</columns> <!-- 52 -->
      </xsl:variable>      

      <!--Invoice hierarchy structure of Invoice/InvoiceLineItemDetail is flattened into a single line per InvoiceLineItemDetail-->
      <xsl:template match="/">
      
            <!--Create header row of attribute names-->
            <xsl:for-each select="msxsl:node-set($headers)/headers">
                  <xsl:text>"</xsl:text>
                  <xsl:value-of select="."/>
                  <xsl:text>"</xsl:text>
                  <xsl:if test="position() != last()">
                        <xsl:value-of select="$DL"/>
                  </xsl:if>
            </xsl:for-each>
            <xsl:value-of select="$CR"/>

            <xsl:for-each select="/*/*/*/*">
                  <xsl:variable name="NodeName" select="name(current())"/>
                  
                  <!-- If node is InvoiceLineItemDetail, get the values based on their element name (from the columns above -->
                  <xsl:if test="$NodeName = 'InvoiceLineItem'">
                        <xsl:variable name="property" select="ancestor-or-self::node()|descendant::node()"/>
                        <xsl:for-each select="msxsl:node-set($columns)/columns">
                              <!-- Extract the column name and value -->                  
                              <xsl:variable name="column" select="."/>      
                              <xsl:variable name="value" select="$property/*[name() = $column]"/>            
                              <xsl:text>"</xsl:text>
                              <xsl:choose>
                                    <xsl:when test="contains($column,'Date')">
                                          <xsl:variable name="year" select="msxsl:format-date($value, 'yyyy')"/>
                                          <xsl:variable name="month" select="msxsl:format-date($value, 'M')"/>
                                          <xsl:variable name="day" select="msxsl:format-date($value, 'd')"/>
                                          <xsl:variable name="isLeap" select="(($year mod 4)=0 and ($year mod 100)!=0) or ($year mod 400)=0"/>
                                          <xsl:choose>
                                                <xsl:when test="$isLeap = true()">
                                                      <xsl:choose>
                                                            <xsl:when test="$month = 1"><xsl:value-of select="($year*1000) + (0 + $day) - 1900000"/></xsl:when>
                                                            <xsl:when test="$month = 2"><xsl:value-of select="($year*1000) + (31 + $day) - 1900000"/></xsl:when> <!-- Jan = 31 -->
                                                            <xsl:when test="$month = 3"><xsl:value-of select="($year*1000) + (60 + $day) - 1900000"/></xsl:when> <!-- Feb = 29 -->
                                                            <xsl:when test="$month = 4"><xsl:value-of select="($year*1000) + (91 + $day) - 1900000"/></xsl:when> <!-- Mar = 31 -->
                                                            <xsl:when test="$month = 5"><xsl:value-of select="($year*1000) + (121 + $day) - 1900000"/></xsl:when> <!-- Apr = 30 -->
                                                            <xsl:when test="$month = 6"><xsl:value-of select="($year*1000) + (152 + $day) - 1900000"/></xsl:when> <!-- May = 31 -->
                                                            <xsl:when test="$month = 7"><xsl:value-of select="($year*1000) + (182 + $day) - 1900000"/></xsl:when> <!-- Jun = 30 -->
                                                            <xsl:when test="$month = 8"><xsl:value-of select="($year*1000) + (213 + $day) - 1900000"/></xsl:when> <!-- Jul = 31 -->
                                                            <xsl:when test="$month = 9"><xsl:value-of select="($year*1000) + (244 + $day) - 1900000"/></xsl:when> <!-- Aug = 31 -->
                                                            <xsl:when test="$month = 10"><xsl:value-of select="($year*1000) + (274 + $day) - 1900000"/></xsl:when> <!-- Sep = 30 -->
                                                            <xsl:when test="$month = 11"><xsl:value-of select="($year*1000) + (305 + $day) - 1900000"/></xsl:when> <!-- Oct = 31 -->
                                                            <xsl:when test="$month = 12"><xsl:value-of select="($year*1000) + (335 + $day) - 1900000"/></xsl:when> <!-- Nov = 30 -->
                                                      </xsl:choose>
                                                </xsl:when>
                                                <xsl:when test="$isLeap = false()">      
                                                      <xsl:choose>
                                                            <xsl:when test="$month = 1"><xsl:value-of select="($year*1000) + (0 + $day) - 1900000"/></xsl:when>
                                                            <xsl:when test="$month = 2"><xsl:value-of select="($year*1000) + (31 + $day) - 1900000"/></xsl:when> <!-- Jan = 31 -->
                                                            <xsl:when test="$month = 3"><xsl:value-of select="($year*1000) + (59 + $day) - 1900000"/></xsl:when> <!-- Feb = 28 -->
                                                            <xsl:when test="$month = 4"><xsl:value-of select="($year*1000) + (90 + $day) - 1900000"/></xsl:when> <!-- Mar = 31 -->
                                                            <xsl:when test="$month = 5"><xsl:value-of select="($year*1000) + (120 + $day) - 1900000"/></xsl:when> <!-- Apr = 30 -->
                                                            <xsl:when test="$month = 6"><xsl:value-of select="($year*1000) + (151 + $day) - 1900000"/></xsl:when> <!-- May = 31 -->
                                                            <xsl:when test="$month = 7"><xsl:value-of select="($year*1000) + (181 + $day) - 1900000"/></xsl:when> <!-- Jun = 30 -->
                                                            <xsl:when test="$month = 8"><xsl:value-of select="($year*1000) + (212 + $day) - 1900000"/></xsl:when> <!-- Jul = 31 -->
                                                            <xsl:when test="$month = 9"><xsl:value-of select="($year*1000) + (243 + $day) - 1900000"/></xsl:when> <!-- Aug = 31 -->
                                                            <xsl:when test="$month = 10"><xsl:value-of select="($year*1000) + (273 + $day) - 1900000"/></xsl:when> <!-- Sep = 30 -->
                                                            <xsl:when test="$month = 11"><xsl:value-of select="($year*1000) + (304 + $day) - 1900000"/></xsl:when> <!-- Oct = 31 -->
                                                            <xsl:when test="$month = 12"><xsl:value-of select="($year*1000) + (334 + $day) - 1900000"/></xsl:when> <!-- Nov = 30 -->
                                                      </xsl:choose>
                                                </xsl:when>
                                          </xsl:choose>
                                    </xsl:when>      
                                    <xsl:otherwise>
                                          <xsl:value-of select="$value" disable-output-escaping="yes"/>
                                    </xsl:otherwise>
                              </xsl:choose>
                              <xsl:text>"</xsl:text>
                              <xsl:if test="position() != last()">
                                    <xsl:value-of select="$DL"/>
                              </xsl:if>
                        </xsl:for-each>
                        <xsl:value-of select="$CR"/>
                  </xsl:if>
            </xsl:for-each>

      </xsl:template>

</xsl:stylesheet>

---------------------------------------------

XML:

<?xml version="1.0" encoding="utf-8"?>
<root xmlns="http://integration.cbre.com/schemas/gcs/batchinvoice/v1">
  <Invoice xmlns="http://integration.cbre.com/schemas/gcs/batchinvoice/v2" xmlns:common="http://integration.cbre.com/schemas/gcs/common/v1">
    <ClientId>ATT</ClientId>
    <OriginatingInvoiceId>1728</OriginatingInvoiceId>
    <VendorId>237006</VendorId>
    <AlternativeVendorId>PRV-08-220</AlternativeVendorId>
    <VendorInvoiceNumber>INV0391656</VendorInvoiceNumber>
    <InvoiceDate>2013-08-12T00:00:00</InvoiceDate>
    <InvoiceReceiveDate>2013-08-17T00:00:00</InvoiceReceiveDate>
    <GLDate>2013-08-21T20:53:03.701-05:00</GLDate>
    <Remark>Please reposition two locks on two of the panels on the sales floor. Caller says that they are not positioned correctly.</Remark>
    <TotalInvoiceAmount>215.18</TotalInvoiceAmount>
    <CurrencyCode>USD</CurrencyCode>
    <StatusCode>
      <common:Code>Authorized</common:Code>
      <common:Description>Authorized</common:Description>
    </StatusCode>
    <ClientApprovalNumber>3000317289</ClientApprovalNumber>
    <InvoiceSource>COR</InvoiceSource>
    <NumberOfLines>4</NumberOfLines>
    <InvoiceLineItems>
      <InvoiceLineItem>
        <OriginatingInvoiceLineID>5444</OriginatingInvoiceLineID>
        <POLineNumber>1</POLineNumber>
        <LineItemDescription>SWAP 2 LOCKS TO DIFFERENT LOCKS. 2013-08-06T00:00:00</LineItemDescription>
        <LocationId>ATR000704</LocationId>
        <BuildingName>1350 Travis Blvd, Suite Z13</BuildingName>
        <LineItemAmount>75.000000</LineItemAmount>
        <GLAccount>
          <CompanyId>51785</CompanyId>
          <GLAccountCode>51785</GLAccountCode>
          <AccountBlock>51785</AccountBlock>
          <GLAccountDescription>51785</GLAccountDescription>
        </GLAccount>
        <AdditionalCoding>
          <ClientPONumber />
          <WorkOrderNumber>0704000001</WorkOrderNumber>
          <WorkOrderCompletedDate>2013-08-06T15:42:36</WorkOrderCompletedDate>
          <WorkOrderCreatedDate>2013-08-01T05:46:00</WorkOrderCreatedDate>
          <CodingBlock1>Labor</CodingBlock1>
          <CodingBlock2>Install New Locks/Keys</CodingBlock2>
          <CodingBlock4>SWAP 2 LOCKS TO DIFFERENT LOCKS.</CodingBlock4>
          <CodingBlock5>Locks/Keys</CodingBlock5>
        </AdditionalCoding>
      </InvoiceLineItem>
      <InvoiceLineItem>
        <OriginatingInvoiceLineID>5445</OriginatingInvoiceLineID>
        <POLineNumber>2</POLineNumber>
        <LineItemDescription>TRIP</LineItemDescription>
        <LocationId>ATR000704</LocationId>
        <BuildingName>1350 Travis Blvd, Suite Z13</BuildingName>
        <LineItemAmount>75.000000</LineItemAmount>
        <GLAccount>
          <CompanyId>51785</CompanyId>
          <GLAccountCode>51785</GLAccountCode>
          <AccountBlock>51785</AccountBlock>
          <GLAccountDescription>51785</GLAccountDescription>
        </GLAccount>
        <AdditionalCoding>
          <ClientPONumber />
          <WorkOrderNumber>0704000001</WorkOrderNumber>
          <WorkOrderCompletedDate>2013-08-06T15:42:36</WorkOrderCompletedDate>
          <WorkOrderCreatedDate>2013-08-01T05:46:00</WorkOrderCreatedDate>
          <CodingBlock1>Miscellaneous</CodingBlock1>
          <CodingBlock2>Install New Locks/Keys</CodingBlock2>
          <CodingBlock4>TRIP</CodingBlock4>
          <CodingBlock5>Locks/Keys</CodingBlock5>
        </AdditionalCoding>
      </InvoiceLineItem>
      <InvoiceLineItem>
        <OriginatingInvoiceLineID>5446</OriginatingInvoiceLineID>
        <POLineNumber>3</POLineNumber>
        <LineItemDescription>NSBS SERVICE FEE</LineItemDescription>
        <LocationId>ATR000704</LocationId>
        <BuildingName>1350 Travis Blvd, Suite Z13</BuildingName>
        <LineItemAmount>60.000000</LineItemAmount>
        <GLAccount>
          <CompanyId>51785</CompanyId>
          <GLAccountCode>51785</GLAccountCode>
          <AccountBlock>51785</AccountBlock>
          <GLAccountDescription>51785</GLAccountDescription>
        </GLAccount>
        <AdditionalCoding>
          <ClientPONumber />
          <WorkOrderNumber>0704000001</WorkOrderNumber>
          <WorkOrderCompletedDate>2013-08-06T15:42:36</WorkOrderCompletedDate>
          <WorkOrderCreatedDate>2013-08-01T05:46:00</WorkOrderCreatedDate>
          <CodingBlock1>Miscellaneous</CodingBlock1>
          <CodingBlock2>Install New Locks/Keys</CodingBlock2>
          <CodingBlock4>NSBS SERVICE FEE</CodingBlock4>
          <CodingBlock5>Locks/Keys</CodingBlock5>
        </AdditionalCoding>
      </InvoiceLineItem>
      <InvoiceLineItem>
        <OriginatingInvoiceLineID>5443</OriginatingInvoiceLineID>
        <POLineNumber>4</POLineNumber>
        <LineItemDescription>Tax</LineItemDescription>
        <LocationId>ATR000704</LocationId>
        <BuildingName>1350 Travis Blvd, Suite Z13</BuildingName>
        <LineItemAmount>5.180000</LineItemAmount>
        <GLAccount>
          <CompanyId>51785</CompanyId>
          <GLAccountCode>51785</GLAccountCode>
          <AccountBlock>51785</AccountBlock>
          <GLAccountDescription>51785</GLAccountDescription>
        </GLAccount>
        <AdditionalCoding>
          <ClientPONumber />
          <WorkOrderNumber>0704000001</WorkOrderNumber>
          <WorkOrderCompletedDate>2013-08-06T15:42:36</WorkOrderCompletedDate>
          <WorkOrderCreatedDate>2013-08-01T05:46:00</WorkOrderCreatedDate>
          <CodingBlock1>Tax</CodingBlock1>
          <CodingBlock2>Install New Locks/Keys</CodingBlock2>
          <CodingBlock4>Tax</CodingBlock4>
          <CodingBlock5>Locks/Keys</CodingBlock5>
        </AdditionalCoding>
      </InvoiceLineItem>
    </InvoiceLineItems>
  </Invoice>
  <Invoice xmlns="http://integration.cbre.com/schemas/gcs/batchinvoice/v2" xmlns:common="http://integration.cbre.com/schemas/gcs/common/v1">
    <ClientId>ATT</ClientId>
    <OriginatingInvoiceId>1752</OriginatingInvoiceId>
    <VendorId>236998</VendorId>
    <AlternativeVendorId>PRV-09-1301</AlternativeVendorId>
    <VendorInvoiceNumber>28</VendorInvoiceNumber>
    <InvoiceDate>2013-08-12T00:00:00</InvoiceDate>
    <InvoiceReceiveDate>2013-08-20T00:00:00</InvoiceReceiveDate>
    <GLDate>2013-08-21T20:53:03.779-05:00</GLDate>
    <Remark />
    <TotalInvoiceAmount>378.59</TotalInvoiceAmount>
    <CurrencyCode>USD</CurrencyCode>
    <StatusCode>
      <common:Code>Authorized</common:Code>
      <common:Description>Authorized</common:Description>
    </StatusCode>
    <ClientApprovalNumber>3000517524</ClientApprovalNumber>
    <InvoiceSource>COR</InvoiceSource>
    <NumberOfLines>2</NumberOfLines>
    <InvoiceLineItems>
      <InvoiceLineItem>
        <OriginatingInvoiceLineID>6790</OriginatingInvoiceLineID>
        <POLineNumber>5</POLineNumber>
        <LineItemDescription>1 man Electrician 2013-08-12T00:00:00</LineItemDescription>
        <LocationId>ATR000596</LocationId>
        <BuildingName>Canyon Rd Store</BuildingName>
        <LineItemAmount>316.000000</LineItemAmount>
        <GLAccount>
          <CompanyId>51310</CompanyId>
          <GLAccountCode>51310</GLAccountCode>
          <AccountBlock>51310</AccountBlock>
          <GLAccountDescription>51310</GLAccountDescription>
        </GLAccount>
        <AdditionalCoding>
          <ClientPONumber />
          <WorkOrderNumber>0596000002</WorkOrderNumber>
          <WorkOrderCompletedDate>2013-08-12T16:46:21</WorkOrderCompletedDate>
          <WorkOrderCreatedDate>2013-08-01T06:06:00</WorkOrderCreatedDate>
          <CodingBlock1>Labor</CodingBlock1>
          <CodingBlock2>Electrical Wiring</CodingBlock2>
          <CodingBlock4>1 man Electrician</CodingBlock4>
          <CodingBlock5>Electrical</CodingBlock5>
        </AdditionalCoding>
      </InvoiceLineItem>
      <InvoiceLineItem>
        <OriginatingInvoiceLineID>6791</OriginatingInvoiceLineID>
        <POLineNumber>6</POLineNumber>
        <LineItemDescription>wiring, euipment, trip charge</LineItemDescription>
        <LocationId>ATR000596</LocationId>
        <BuildingName>Canyon Rd Store</BuildingName>
        <LineItemAmount>62.590000</LineItemAmount>
        <GLAccount>
          <CompanyId>51310</CompanyId>
          <GLAccountCode>51310</GLAccountCode>
          <AccountBlock>51310</AccountBlock>
          <GLAccountDescription>51310</GLAccountDescription>
        </GLAccount>
        <AdditionalCoding>
          <ClientPONumber />
          <WorkOrderNumber>0596000002</WorkOrderNumber>
          <WorkOrderCompletedDate>2013-08-12T16:46:21</WorkOrderCompletedDate>
          <WorkOrderCreatedDate>2013-08-01T06:06:00</WorkOrderCreatedDate>
          <CodingBlock1>Miscellaneous</CodingBlock1>
          <CodingBlock2>Electrical Wiring</CodingBlock2>
          <CodingBlock4>wiring, euipment, trip charge</CodingBlock4>
          <CodingBlock5>Electrical</CodingBlock5>
        </AdditionalCoding>
      </InvoiceLineItem>
    </InvoiceLineItems>
  </Invoice>
</root>



Thanks
Avatar of Mani Pazhana
Mani Pazhana
Flag of United States of America image

ASKER

Here is the schema:

<?xml version="1.0" encoding="utf-8"?>
<xs:schema xmlns="http://integration.cbre.com/schemas/gcs/batchinvoice/v2" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:common="http://integration.cbre.com/schemas/gcs/common/v1" targetNamespace="http://integration.cbre.com/schemas/gcs/batchinvoice/v2" elementFormDefault="qualified">
            <!-- Include -->
      <xs:import namespace="http://integration.cbre.com/schemas/gcs/common/v1" schemaLocation="integration.cbre.com.schemas.gcs.common.v1.xsd"/>
      <!-- XSD -->
      <xs:complexType name="GLAccountType">
            <xs:sequence>
                  <xs:element name="CompanyId" type="xs:string" nillable="true">
                        <xs:annotation>
                              <xs:documentation>Financial System (i.e. JDE) Company Code</xs:documentation>
                        </xs:annotation>
                  </xs:element>
                  <xs:element name="GLAccountCode" type="xs:string" nillable="true">
                        <xs:annotation>
                              <xs:documentation>GL Account Code (In JDE this represents the object account)</xs:documentation>
                        </xs:annotation>
                  </xs:element>
                  <xs:element name="Subsidiary" type="xs:string" nillable="true" minOccurs="0">
                        <xs:annotation>
                              <xs:documentation>Subsidiary account</xs:documentation>
                        </xs:annotation>
                  </xs:element>
                  <xs:element name="SubLedger" type="xs:string" nillable="true" minOccurs="0">
                        <xs:annotation>
                              <xs:documentation>More granular GL account code</xs:documentation>
                        </xs:annotation>
                  </xs:element>
                  <xs:element name="SubLedgerType" type="xs:string" nillable="true" minOccurs="0">
                        <xs:annotation>
                              <xs:documentation>Indicates the type of subledger, not required for all clients</xs:documentation>
                        </xs:annotation>
                  </xs:element>
                  <xs:element name="LedgerType" type="xs:string" nillable="true" minOccurs="0">
                        <xs:annotation>
                              <xs:documentation>Indicates the type of ledger</xs:documentation>
                        </xs:annotation>
                  </xs:element>
                  <xs:element name="CostCenter" type="xs:string" nillable="true" minOccurs="0">
                        <xs:annotation>
                              <xs:documentation>Cost Center</xs:documentation>
                        </xs:annotation>
                  </xs:element>
                  <xs:element name="AccountBlock" type="xs:string" nillable="true" minOccurs="0">
                        <xs:annotation>
                              <xs:documentation>Combined Object Account.Subsidiary field</xs:documentation>
                        </xs:annotation>
                  </xs:element>
                  <xs:element name="GLAccountDescription" type="xs:string" nillable="true" minOccurs="0">
                        <xs:annotation>
                              <xs:documentation>Description of the GL Account</xs:documentation>
                        </xs:annotation>
                  </xs:element>
                  <xs:element name="ClientAccountCode" type="xs:string" nillable="true" minOccurs="0">
                        <xs:annotation>
                              <xs:documentation>Client Account Code (usually based on mapping Client's GL Account Codes to CBRE GL Account Codes)</xs:documentation>
                        </xs:annotation>
                  </xs:element>
            </xs:sequence>
      </xs:complexType>
      <xs:complexType name="AdditionalCode">
            <xs:sequence>
                  <xs:element name="WBSCode" type="xs:string" nillable="true" minOccurs="0">
                        <xs:annotation>
                              <xs:documentation>For Project Expenses, indicates the WBS associated with the expenses</xs:documentation>
                        </xs:annotation>
                  </xs:element>
                  <xs:element name="OriginatingProjectNumber" type="xs:string" nillable="true" minOccurs="0">
                        <xs:annotation>
                              <xs:documentation>For Project Expenses, indicates the project number associated with the expenses</xs:documentation>
                        </xs:annotation>
                  </xs:element>
                  <xs:element name="OriginatingProjectSystem" type="xs:string" nillable="true" minOccurs="0">
                        <xs:annotation>
                              <xs:documentation>For Project Expenses, indicates the originating system</xs:documentation>
                        </xs:annotation>
                  </xs:element>
                  <xs:element name="ClientPONumber" type="xs:string" nillable="true" minOccurs="0">
                        <xs:annotation>
                              <xs:documentation>Client's Purchase Order Number</xs:documentation>
                        </xs:annotation>
                  </xs:element>
                  <xs:element name="WorkOrderNumber" type="xs:string" nillable="true" minOccurs="0">
                        <xs:annotation>
                              <xs:documentation>For FM Expenses, indicates the work order associated with the expense</xs:documentation>
                        </xs:annotation>
                  </xs:element>
                  <xs:element name="WorkOrderCompletedDate" type="xs:dateTime" minOccurs="0">
                        <xs:annotation>
                              <xs:documentation>For FM Expenses, indicates the work order completion date</xs:documentation>
                        </xs:annotation>
                  </xs:element>
                  <xs:element name="WorkOrderCreatedDate" type="xs:dateTime" minOccurs="0">
                        <xs:annotation>
                              <xs:documentation>For FM Expenses, indicates the work order creation date</xs:documentation>
                        </xs:annotation>
                  </xs:element>
                  <xs:element name="CodingBlock1" type="xs:string" nillable="true" minOccurs="0">
                        <xs:annotation>
                              <xs:documentation>Additional string field to capture coding information about the transactions</xs:documentation>
                        </xs:annotation>
                  </xs:element>
                  <xs:element name="CodingBlock2" type="xs:string" nillable="true" minOccurs="0">
                        <xs:annotation>
                              <xs:documentation>Additional string field to capture coding information about the transactions</xs:documentation>
                        </xs:annotation>
                  </xs:element>
                  <xs:element name="CodingBlock3" type="xs:string" nillable="true" minOccurs="0">
                        <xs:annotation>
                              <xs:documentation>Additional string field to capture coding information about the transactions</xs:documentation>
                        </xs:annotation>
                  </xs:element>
                  <xs:element name="CodingBlock4" type="xs:string" nillable="true" minOccurs="0">
                        <xs:annotation>
                              <xs:documentation>Additional string field to capture coding information about the transactions</xs:documentation>
                        </xs:annotation>
                  </xs:element>
                  <xs:element name="CodingBlock5" type="xs:string" nillable="true" minOccurs="0">
                        <xs:annotation>
                              <xs:documentation>Additional string field to capture coding information about the transactions</xs:documentation>
                        </xs:annotation>
                  </xs:element>
                  <xs:element name="CodingBlock6" type="xs:string" nillable="true" minOccurs="0">
                        <xs:annotation>
                              <xs:documentation>Additional string field to capture coding information about the transactions</xs:documentation>
                        </xs:annotation>
                  </xs:element>
                  <xs:element name="CodingDate1" type="xs:dateTime" nillable="true" minOccurs="0">
                        <xs:annotation>
                              <xs:documentation>Additional datetime field to capture coding information about the transactions</xs:documentation>
                        </xs:annotation>
                  </xs:element>
                  <xs:element name="CodingDate2" type="xs:dateTime" nillable="true" minOccurs="0">
                        <xs:annotation>
                              <xs:documentation>Additional datetime field to capture coding information about the transactions</xs:documentation>
                        </xs:annotation>
                  </xs:element>
            </xs:sequence>
      </xs:complexType>
      <xs:complexType name="InvoiceLineItemType">
            <xs:sequence>
                  <xs:element name="OriginatingInvoiceLineID" type="xs:int">
                        <xs:annotation>
                              <xs:documentation>Indicatesthe line item on the invoice (based on the system generating the invoice)</xs:documentation>
                        </xs:annotation>
                  </xs:element>
                  <xs:element name="POLineNumber" type="xs:int" minOccurs="0">
                        <xs:annotation>
                              <xs:documentation>Purchase order line number</xs:documentation>
                        </xs:annotation>
                  </xs:element>
                  <xs:element name="LineItemDescription" type="xs:string" nillable="true">
                        <xs:annotation>
                              <xs:documentation>Description of the line item</xs:documentation>
                        </xs:annotation>
                  </xs:element>
                  <xs:element name="LocationId" type="xs:string" nillable="true">
                        <xs:annotation>
                              <xs:documentation>Location Id for the building where the charge was incurred.  For JDE, this is the JDE Business Unit</xs:documentation>
                        </xs:annotation>
                  </xs:element>
                  <xs:element name="BuildingName" type="xs:string" nillable="true" minOccurs="0">
                        <xs:annotation>
                              <xs:documentation>Building/Location Name</xs:documentation>
                        </xs:annotation>
                  </xs:element>
                  <xs:element name="LineItemAmount" type="xs:decimal">
                        <xs:annotation>
                              <xs:documentation>Amount by line amount broken out by GL Account/Additional Coding</xs:documentation>
                        </xs:annotation>
                  </xs:element>
                  <xs:element name="TaxableLineItemAmount" type="xs:decimal" minOccurs="0">
                        <xs:annotation>
                              <xs:documentation>Amount of the line item that is taxable</xs:documentation>
                        </xs:annotation>
                  </xs:element>
                  <xs:element name="TaxAmountLineItem" type="xs:decimal" minOccurs="0">
                        <xs:annotation>
                              <xs:documentation>Amount of tax calculated on the line item (based on calculation in originating system)</xs:documentation>
                        </xs:annotation>
                  </xs:element>
                  <xs:element name="GLAccount" type="GLAccountType" nillable="true"/>
                  <xs:element name="AdditionalCoding" type="AdditionalCode" nillable="true" minOccurs="0"/>
            </xs:sequence>
      </xs:complexType>
      <xs:complexType name="ArrayOfInvoiceLineItem">
            <xs:sequence>
                  <xs:element name="InvoiceLineItem" type="InvoiceLineItemType" nillable="true" minOccurs="0" maxOccurs="unbounded"/>
            </xs:sequence>
      </xs:complexType>
      <xs:complexType name="InvoiceType">
            <xs:sequence>
                  <xs:element name="ClientId" type="xs:string" nillable="true">
                        <xs:annotation>
                              <xs:documentation>Unique Client Identifier</xs:documentation>
                        </xs:annotation>
                  </xs:element>
                  <xs:element name="OriginatingInvoiceId" type="xs:string" nillable="true">
                        <xs:annotation>
                              <xs:documentation>System generated invoice number in the originating system</xs:documentation>
                        </xs:annotation>
                  </xs:element>
                  <xs:element name="VendorId" type="xs:string" nillable="true">
                        <xs:annotation>
                              <xs:documentation>Corresponding Vendor Identifier (based on the financial system)</xs:documentation>
                        </xs:annotation>
                  </xs:element>
                  <xs:element name="AlternativeVendorId" type="xs:string" nillable="true" minOccurs="0">
                        <xs:annotation>
                              <xs:documentation>Alternative Id associated with this vendor</xs:documentation>
                        </xs:annotation>
                  </xs:element>
                  <xs:element name="VendorInvoiceNumber" type="xs:string" nillable="true">
                        <xs:annotation>
                              <xs:documentation>Supplier/Vendor Invoice Number</xs:documentation>
                        </xs:annotation>
                  </xs:element>
                  <xs:element name="InvoiceDate" type="xs:dateTime">
                        <xs:annotation>
                              <xs:documentation>Supplier/Vendor Invoice Date</xs:documentation>
                        </xs:annotation>
                  </xs:element>
                  <xs:element name="InvoiceReceiveDate" type="xs:dateTime">
                        <xs:annotation>
                              <xs:documentation>Date the vendor's invoice was received pr scanned</xs:documentation>
                        </xs:annotation>
                  </xs:element>
                  <xs:element name="GLDate" type="xs:dateTime">
                        <xs:annotation>
                              <xs:documentation>Date invoice was posted to the General Ledger</xs:documentation>
                        </xs:annotation>
                  </xs:element>
                  <xs:element name="Remark" type="xs:string" nillable="true">
                        <xs:annotation>
                              <xs:documentation>Remark that will appear on the pay stub</xs:documentation>
                        </xs:annotation>
                  </xs:element>
                  <xs:element name="TotalInvoiceAmount" type="xs:decimal">
                        <xs:annotation>
                              <xs:documentation>Total amount of the invoice (should be the sum of the line items)</xs:documentation>
                        </xs:annotation>
                  </xs:element>
                  <xs:element name="CurrencyCode" type="xs:string" nillable="true">
                        <xs:annotation>
                              <xs:documentation>Currency code that all monetary values are reported on the invoice</xs:documentation>
                        </xs:annotation>
                  </xs:element>
                  <xs:element name="TaxRateAreaCode" type="xs:string" nillable="true" minOccurs="0">
                        <xs:annotation>
                              <xs:documentation>Tax Rate applied to this invoice (only required if the invoice has taxable line items)</xs:documentation>
                        </xs:annotation>
                  </xs:element>
                  <xs:element name="StatusCode" type="common:Codes" nillable="true">
                        <xs:annotation>
                              <xs:documentation>Indicates the status of the invoice</xs:documentation>
                        </xs:annotation>
                  </xs:element>
                  <xs:element name="ClientApprovalNumber" type="xs:string" nillable="true" minOccurs="0">
                        <xs:annotation>
                              <xs:documentation>Client's approval number for the submittal</xs:documentation>
                        </xs:annotation>
                  </xs:element>
                  <xs:element name="AuthorizedBy" type="xs:string" nillable="true" minOccurs="0">
                        <xs:annotation>
                              <xs:documentation>Id of the user that approved the invoice</xs:documentation>
                        </xs:annotation>
                  </xs:element>
                  <xs:element name="BatchReferenceNumber" type="xs:string" nillable="true" minOccurs="0">
                        <xs:annotation>
                              <xs:documentation>Reference number assigned to a batch of invoices</xs:documentation>
                        </xs:annotation>
                  </xs:element>
                  <xs:element name="InvoiceSource" type="xs:string" nillable="true" minOccurs="0">
                        <xs:annotation>
                              <xs:documentation>Source of the invoice (i.e. Corrigo, Expesite)</xs:documentation>
                        </xs:annotation>
                  </xs:element>
                  <xs:element name="NumberOfLines" type="xs:int" minOccurs="0">
                        <xs:annotation>
                              <xs:documentation>Number of lines on the invoice</xs:documentation>
                        </xs:annotation>
                  </xs:element>
                  <xs:element name="InvoiceLineItems" type="ArrayOfInvoiceLineItem" nillable="true" minOccurs="0" maxOccurs="unbounded">
                        <xs:annotation>
                              <xs:documentation>Line items for the invoice</xs:documentation>
                        </xs:annotation>
                  </xs:element>
            </xs:sequence>
      </xs:complexType>
      <xs:complexType name="roottype">
            <xs:sequence>
                  <xs:element name="Invoice" type="InvoiceType" nillable="true" minOccurs="0" maxOccurs="unbounded"/>
            </xs:sequence>
      </xs:complexType>
      <xs:element name="root" type="roottype" nillable="true"/>
</xs:schema>
ASKER CERTIFIED SOLUTION
Avatar of zc2
zc2
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks
Welcome.