小贝子编程

将XML层次结构展平为CSV的最佳方法是什么



我在将XML文件转换为平面csv文件时遇到问题。我似乎找不到一个很好的方法来压平xml文件。我试过很多我在网上看到的东西，但我只能找到一个适合我的网站(http://convertcsv.com/xml-to-csv.htm)这按照我需要的方式对其进行格式化，但我需要将我的进程封装在SSIS包中，这样使用网站就无法工作。

我使用C#读取XML文件，然后使用XslCompiledTransform 进行转换

这是源文件。

<?xml version="1.0" encoding="utf-8"?>
<GTINItemInfoProxy xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://www.xxxxx.com/schemas/GS1">
<header version="2.0" xmlns="">
<messageId>00000000000001.US.00000008</messageId>
<creationDateTime>2019-02-08T11:52:15</creationDateTime>
<businessUnit>Example</businessUnit>
<sourceSystem>HCS</sourceSystem>
</header>
<GTINItemInfo version="2.0" xmlns="">
<item>
<gtin>00000000000001</gtin>
<informationProviderGLN>0000000000001</informationProviderGLN>
<internalId>24008</internalId>
<state>RELEASED</state>
<lastStateModifiedDate>0001-01-01T00:00:00</lastStateModifiedDate>
<internalProductDescription>Example</internalProductDescription>
<effectiveDate>2018-06-20T14:25:09</effectiveDate>
<globalAttributes>
<productType>EA</productType>
<globalClassificationCategory>
<code>3213213213</code>
</globalClassificationCategory>
<gtinNameList>
<gtinName lang="en">Example</gtinName>
</gtinNameList>
<brandName>Example</brandName>
<brandOwnerGLN>000010001010</brandOwnerGLN>
<netContentList>
<netContent uom="EA">1</netContent>
</netContentList>
<totalQuantityOfNextLowerTradeItem>1</totalQuantityOfNextLowerTradeItem>
<isBaseUnit>Yes</isBaseUnit>
<isConsumerUnit>Yes</isConsumerUnit>
<isVariableWeightItem>No</isVariableWeightItem>
</globalAttributes>
<targetMarketList>
<targetMarketAttributes>
<targetMarket>US</targetMarket>
<alternateItemIdentificationList>
<alternateItemIdentification>
<agency>Example</agency>
<id>31321</id>
</alternateItemIdentification>
<alternateItemIdentification>
<agency>Example</agency>
<id>1</id>
</alternateItemIdentification>
</alternateItemIdentificationList>
<shortDescriptionList>
<shortDescription lang="en">Example</shortDescription>
</shortDescriptionList>
<productDescriptionList>
<productDescription lang="en">Example</productDescription>
</productDescriptionList>
<additionalDescriptionList>
<additionalDescription lang="en">Example</additionalDescription>
</additionalDescriptionList>
<isDispatchUnitList>
<isDispatchUnit>No</isDispatchUnit>
</isDispatchUnitList>
<isInvoiceUnitList>
<isInvoiceUnit>No</isInvoiceUnit>
</isInvoiceUnitList>
<isOrderableUnitList>
<isOrderableUnit>No</isOrderableUnit>
</isOrderableUnitList>
<packagingMarkedReturnable>No</packagingMarkedReturnable>
<minimumTradeItemLifespanFromProductionList>
<minimumTradeItemLifespanFromProduction>1825</minimumTradeItemLifespanFromProduction>
</minimumTradeItemLifespanFromProductionList>
<nonGTINPalletHi>0</nonGTINPalletHi>
<nonGTINPalletTi>0</nonGTINPalletTi>
<numberOfItemsPerPallet>0</numberOfItemsPerPallet>
<hasBatchNumber>Yes</hasBatchNumber>
<productMarkedRecyclable>No</productMarkedRecyclable>
<depth uom="in">12</depth>
<height uom="in">8</height>
<width uom="in">12</width>
<grossWeight uom="lb">0.3213</grossWeight>
<netWeight uom="lb">0.3213</netWeight>
<totalUnitsPerCase>1</totalUnitsPerCase>
<preDefinedFlex>
<alternateClassificationList>
<alternateClassification>
<code>Example</code>
<scheme>Example</scheme>
</alternateClassification>
<alternateClassification>
<code>Example</code>
<scheme>Example</scheme>
</alternateClassification>
<alternateClassification>
<code>Example</code>
<scheme>Example</scheme>
</alternateClassification>
<alternateClassification>
<code>Example</code>
<scheme>Example</scheme>
</alternateClassification>
<alternateClassification>
<code>Example</code>
<scheme>Example</scheme>
</alternateClassification>
</alternateClassificationList>
<brandOwnerAdditionalTradeItemIdentificationList>
<brandOwnerAdditionalTradeItemIdentification>
<brandOwnerAdditionalIdType>Example</brandOwnerAdditionalIdType>
<brandOwnerAdditionalIdValue>Example</brandOwnerAdditionalIdValue>
</brandOwnerAdditionalTradeItemIdentification>
</brandOwnerAdditionalTradeItemIdentificationList>
<consumerSalesConditionList>
<consumerSalesCondition>FALSE</consumerSalesCondition>
</consumerSalesConditionList>
<countryOfOriginList>
<countryOfOrigin>US</countryOfOrigin>
</countryOfOriginList>
<dataCarrierList>
<dataCarrierTypeCode>Example</dataCarrierTypeCode>
</dataCarrierList>
<donationIdentificationNumberMarked>No</donationIdentificationNumberMarked>
<doesTradeItemContainLatex>No</doesTradeItemContainLatex>
<exemptFromFDAPreMarketAuthorization>No</exemptFromFDAPreMarketAuthorization>
<fDA510KPremarketAuthorization>Example</fDA510KPremarketAuthorization>
<fDAMedicalDeviceListingList>
<fDAMedicalDeviceListing>Example</fDAMedicalDeviceListing>
</fDAMedicalDeviceListingList>
<gs1TradeItemIdentificationKey>
<code>Example</code>
<value>14</value>
</gs1TradeItemIdentificationKey>
<isTradeItemManagedByManufactureDate>true</isTradeItemManagedByManufactureDate>
<manufacturerList>
<manufacturer>
<gln>0100000000000</gln>
</manufacturer>
</manufacturerList>
<manufacturerDeclaredReusabilityType>SINGLE_USE</manufacturerDeclaredReusabilityType>
<mRICompatibilityCode>UNSPECIFIED</mRICompatibilityCode>
<serialNumberLocationCodeList>
<serialNumberLocationCode>NOT_MARKED</serialNumberLocationCode>
</serialNumberLocationCodeList>
<tradeChannelList>
<tradeChannel>Example</tradeChannel>
</tradeChannelList>
<tradeItemContactInfoList>
<tradeItemContactInfo>
<availableTime lang="en">2019-02-08T00:00:00</availableTime>
<contactInfoGLN>0000000000002</contactInfoGLN>
<contactType>ABC</contactType>
<targetMarketCommunicationChannel>
<communicationChannelList>
<communicationChannel>
<communicationChannelCode>TELEPHONE</communicationChannelCode>
</communicationChannel>
</communicationChannelList>
</targetMarketCommunicationChannel>
</tradeItemContactInfo>
</tradeItemContactInfoList>
<uDIDDeviceCount>1</uDIDDeviceCount>
</preDefinedFlex>
</targetMarketAttributes>
<targetMarketAttributes>
<targetMarket>CA</targetMarket>
<alternateItemIdentificationList>
<alternateItemIdentification>
<agency>AB</agency>
<id>123245</id>
</alternateItemIdentification>
<alternateItemIdentification>
<agency>ABC</agency>
<id>1</id>
</alternateItemIdentification>
</alternateItemIdentificationList>
<shortDescriptionList>
<shortDescription lang="en">Something</shortDescription>
</shortDescriptionList>
<productDescriptionList>
<productDescription lang="en">Something</productDescription>
</productDescriptionList>
<isDispatchUnitList>
<isDispatchUnit>No</isDispatchUnit>
</isDispatchUnitList>
<isInvoiceUnitList>
<isInvoiceUnit>No</isInvoiceUnit>
</isInvoiceUnitList>
<isOrderableUnitList>
<isOrderableUnit>No</isOrderableUnit>
</isOrderableUnitList>
<packagingMarkedReturnable>No</packagingMarkedReturnable>
<minimumTradeItemLifespanFromProductionList>
<minimumTradeItemLifespanFromProduction>1234</minimumTradeItemLifespanFromProduction>
</minimumTradeItemLifespanFromProductionList>
<nonGTINPalletHi>0</nonGTINPalletHi>
<nonGTINPalletTi>0</nonGTINPalletTi>
<numberOfItemsPerPallet>0</numberOfItemsPerPallet>
<hasBatchNumber>Yes</hasBatchNumber>
<productMarkedRecyclable>No</productMarkedRecyclable>
<depth uom="in">1</depth>
<height uom="in">1</height>
<width uom="in">1</width>
<grossWeight uom="lb">0.3</grossWeight>
<netWeight uom="lb">0.2</netWeight>
<totalUnitsPerCase>1</totalUnitsPerCase>
<unitsPerConsumerUnit>1</unitsPerConsumerUnit>
<preDefinedFlex>
<alternateClassificationList>
<alternateClassification>
<code>1234657</code>
<scheme>Example</scheme>
</alternateClassification>
<alternateClassification>
<code>M000</code>
<scheme>Example</scheme>
</alternateClassification>
<alternateClassification>
<code>K000000</code>
<scheme>Example</scheme>
</alternateClassification>
<alternateClassification>
<code>acb</code>
<scheme>Example</scheme>
</alternateClassification>
<alternateClassification>
<code>12346</code>
<scheme>XXXX</scheme>
</alternateClassification>
</alternateClassificationList>
<brandOwnerAdditionalTradeItemIdentificationList>
<brandOwnerAdditionalTradeItemIdentification>
<brandOwnerAdditionalIdType>XXXX</brandOwnerAdditionalIdType>
<brandOwnerAdditionalIdValue>100000000</brandOwnerAdditionalIdValue>
</brandOwnerAdditionalTradeItemIdentification>
</brandOwnerAdditionalTradeItemIdentificationList>
<canadaSpecificAttributes>
<operatorDescriptionList>
<operatorDescription lang="en">"Sample"</operatorDescription>
</operatorDescriptionList>
</canadaSpecificAttributes>
<consumerSalesConditionList>
<consumerSalesCondition>FALSE</consumerSalesCondition>
</consumerSalesConditionList>
<countryOfOriginList>
<countryOfOrigin>US</countryOfOrigin>
</countryOfOriginList>
<dataCarrierList>
<dataCarrierTypeCode>128</dataCarrierTypeCode>
</dataCarrierList>
<donationIdentificationNumberMarked>No</donationIdentificationNumberMarked>
<doesTradeItemContainLatex>No</doesTradeItemContainLatex>
<exemptFromFDAPreMarketAuthorization>No</exemptFromFDAPreMarketAuthorization>
<fDA510KPremarketAuthorization>K000000</fDA510KPremarketAuthorization>
<fDAMedicalDeviceListingList>
<fDAMedicalDeviceListing>D000000</fDAMedicalDeviceListing>
</fDAMedicalDeviceListingList>
<gs1TradeItemIdentificationKey>
<code>Code</code>
<value>14</value>
</gs1TradeItemIdentificationKey>
<isTradeItemManagedByManufactureDate>true</isTradeItemManagedByManufactureDate>
<manufacturerList>
<manufacturer>
<gln>0100000000000</gln>
</manufacturer>
</manufacturerList>
<manufacturerDeclaredReusabilityType>SINGLE_USE</manufacturerDeclaredReusabilityType>
<mRICompatibilityCode>UNSPECIFIED</mRICompatibilityCode>
<serialNumberLocationCodeList>
<serialNumberLocationCode>NOT_MARKED</serialNumberLocationCode>
</serialNumberLocationCodeList>
<tradeChannelList>
<tradeChannel>TradeChanngelExample</tradeChannel>
</tradeChannelList>
<tradeItemContactInfoList>
<tradeItemContactInfo>
<availableTime lang="en">2019-02-08T00:00:00</availableTime>
<contactInfoGLN>0000000000002</contactInfoGLN>
<contactType>CYC</contactType>
<targetMarketCommunicationChannel>
<communicationChannelList>
<communicationChannel>
<communicationChannelCode>TELEPHONE</communicationChannelCode>
</communicationChannel>
</communicationChannelList>
</targetMarketCommunicationChannel>
</tradeItemContactInfo>
</tradeItemContactInfoList>
<tradeItemMarketingMessageList>
<tradeItemMarketingMessage lang="en">"SampleMessage"</tradeItemMarketingMessage>
</tradeItemMarketingMessageList>
<uDIDDeviceCount>1</uDIDDeviceCount>
</preDefinedFlex>
</targetMarketAttributes>
</targetMarketList>
</item>
</GTINItemInfo>
</GTINItemInfoProxy>

我不想使用xslt方法，但我真的不知道有什么更好的方法。

XslCompiledTransform transform = new XslCompiledTransform();
XsltSettings xsltSettings = new XsltSettings();
xsltSettings.EnableDocumentFunction = true;
transform.Load("XML_Flatten.xslt", xsltSettings, null);
System.IO.File.Delete(@"C:TestingsourceFile_Flat.csv");
transform.Transform(@"C:TestingsourceFile.XML", 
@"C:TestingsourceFile_Flat.csv");

这是我为xslt尝试的一个示例

<xsl:strip-space elements="*" />
<xsl:template match="/*/child::*">
<xsl:for-each select="child::*">
<xsl:if test="position() != last()">
"<xsl:value-of select="normalize-space(.)"/>",
</xsl:if>
<xsl:if test="position()  = last()">
"<xsl:value-of select="normalize-space(.)"/>"<xsl:text>&#xD;</xsl:text>
</xsl:if>
</xsl:for-each>
</xsl:template>
</xsl:stylesheet>

我试图实现的是CSV的输出被压平。类似于这里的结果。http://convertcsv.com/xml-to-csv.htm

我希望列名与XML标记相匹配。但这是额外的奖励。

这是我期望的格式。https://pastebin.com/G7M08dj6

targetMarket,alternateItemIdentificationList/alternateItemIdentification/0/agency,alternateItemIdentificationList/alternateItemIdentification/0/id,alternateItemIdentificationList/alternateItemIdentification/1/agency,alternateItemIdentificationList/alternateItemIdentification/1/id,shortDescriptionList/shortDescription/_lang,shortDescriptionList/shortDescription/__text,productDescriptionList/productDescription/_lang,productDescriptionList/productDescription/__text,additionalDescriptionList/additionalDescription/_lang,additionalDescriptionList/additionalDescription/__text,isDispatchUnitList/isDispatchUnit,isInvoiceUnitList/isInvoiceUnit,isOrderableUnitList/isOrderableUnit,packagingMarkedReturnable,minimumTradeItemLifespanFromProductionList/minimumTradeItemLifespanFromProduction,nonGTINPalletHi,nonGTINPalletTi,numberOfItemsPerPallet,hasBatchNumber,productMarkedRecyclable,depth/_uom,depth/__text,height/_uom,height/__text,width/_uom,width/__text,grossWeight/_uom,grossWeight/__text,netWeight/_uom,netWeight/__text,totalUnitsPerCase,preDefinedFlex/alternateClassificationList/alternateClassification/0/code,preDefinedFlex/alternateClassificationList/alternateClassification/0/scheme,preDefinedFlex/alternateClassificationList/alternateClassification/1/code,preDefinedFlex/alternateClassificationList/alternateClassification/1/scheme,preDefinedFlex/alternateClassificationList/alternateClassification/2/code,preDefinedFlex/alternateClassificationList/alternateClassification/2/scheme,preDefinedFlex/alternateClassificationList/alternateClassification/3/code,preDefinedFlex/alternateClassificationList/alternateClassification/3/scheme,preDefinedFlex/alternateClassificationList/alternateClassification/4/code,preDefinedFlex/alternateClassificationList/alternateClassification/4/scheme,preDefinedFlex/brandOwnerAdditionalTradeItemIdentificationList/brandOwnerAdditionalTradeItemIdentification/brandOwnerAdditionalIdType,preDefinedFlex/brandOwnerAdditionalTradeItemIdentificationList/brandOwnerAdditionalTradeItemIdentification/brandOwnerAdditionalIdValue,preDefinedFlex/consumerSalesConditionList/consumerSalesCondition,preDefinedFlex/countryOfOriginList/countryOfOrigin,preDefinedFlex/dataCarrierList/dataCarrierTypeCode,preDefinedFlex/donationIdentificationNumberMarked,preDefinedFlex/doesTradeItemContainLatex,preDefinedFlex/exemptFromFDAPreMarketAuthorization,preDefinedFlex/fDA510KPremarketAuthorization,preDefinedFlex/fDAMedicalDeviceListingList/fDAMedicalDeviceListing,preDefinedFlex/gs1TradeItemIdentificationKey/code,preDefinedFlex/gs1TradeItemIdentificationKey/value,preDefinedFlex/isTradeItemManagedByManufactureDate,preDefinedFlex/manufacturerList/manufacturer/gln,preDefinedFlex/manufacturerDeclaredReusabilityType,preDefinedFlex/mRICompatibilityCode,preDefinedFlex/serialNumberLocationCodeList/serialNumberLocationCode,preDefinedFlex/tradeChannelList/tradeChannel,preDefinedFlex/tradeItemContactInfoList/tradeItemContactInfo/availableTime/_lang,preDefinedFlex/tradeItemContactInfoList/tradeItemContactInfo/availableTime/__text,preDefinedFlex/tradeItemContactInfoList/tradeItemContactInfo/contactInfoGLN,preDefinedFlex/tradeItemContactInfoList/tradeItemContactInfo/contactType,preDefinedFlex/tradeItemContactInfoList/tradeItemContactInfo/targetMarketCommunicationChannel/communicationChannelList/communicationChannel/communicationChannelCode,preDefinedFlex/uDIDDeviceCount,unitsPerConsumerUnit,preDefinedFlex/canadaSpecificAttributes/operatorDescriptionList/operatorDescription/_lang,preDefinedFlex/canadaSpecificAttributes/operatorDescriptionList/operatorDescription/__text,preDefinedFlex/tradeItemMarketingMessageList/tradeItemMarketingMessage/_lang,preDefinedFlex/tradeItemMarketingMessageList/tradeItemMarketingMessage/__text
US,Example,31321,Example,1,en,Example,en,Example,en,Example,No,No,No,No,1825,0,0,0,Yes,No,in,12,in,8,in,12,lb,0.3213,lb,0.3213,1,Example,Example,Example,Example,Example,Example,Example,Example,Example,Example,Example,Example,FALSE,US,Example,No,No,No,Example,Example,Example,14,true,0100000000000,SINGLE_USE,UNSPECIFIED,NOT_MARKED,Example,en,2019-02-08T00:00:00,0000000000002,ABC,TELEPHONE,1,,,,,
CA,AB,123245,ABC,1,en,Something,en,Something,,,No,No,No,No,1234,0,0,0,Yes,No,in,1,in,1,in,1,lb,0.3,lb,0.2,1,1234657,Example,M000,Example,K000000,Example,acb,Example,12346,XXXX,XXXX,100000000,FALSE,US,128,No,No,No,K000000,D000000,Code,14,true,0100000000000,SINGLE_USE,UNSPECIFIED,NOT_MARKED,TradeChanngelExample,en,2019-02-08T00:00:00,0000000000002,CYC,TELEPHONE,1,1,en,"""Sample""",en,"""SampleMessage"""

下面是一个可以用作起点的示例：

XS:T 1.0

<xsl:stylesheet version="1.0" 
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:gs="http://www.xxxxx.com/schemas/xxx">
<xsl:output method="text" encoding="utf-8"/>
<xsl:template match="/gs:ItemInfoProxy">
<!-- header -->
<xsl:text>targetMarket,agency1,id1,shortDescription1,lang1, &#10;</xsl:text>
<!-- data -->
<xsl:for-each select="ItemInfo/item/targetMarketList/targetMarketAttributes">
<xsl:value-of select="targetMarket" />
<xsl:text>,</xsl:text>
<xsl:value-of select="alternateItemIdentificationList/alternateItemIdentification[1]/agency" />
<xsl:text>,</xsl:text>
<xsl:value-of select="alternateItemIdentificationList/alternateItemIdentification[1]/id" />
<xsl:text>,</xsl:text>
<xsl:value-of select="shortDescriptionList/shortDescription[1]" />
<xsl:text>,</xsl:text>
<xsl:value-of select="shortDescriptionList/shortDescription[1]/@lang" />
<xsl:text>&#10;</xsl:text>
</xsl:for-each>
</xsl:template>
</xsl:stylesheet>

应用于您的输入示例，结果将是：

targetMarket,agency1,id1,shortDescription1,lang1, 
US,Example,31321,Example,en
CA,AB,123245,Something,en

相关内容

最新更新


热门标签：

javascript python java c# php android html jquery c++ css ios sql mysql arrays asp.net json python-3.x ruby-on-rails .net sql-server django objective-c excel regex ruby linux ajax iphone xml vba spring asp.net-mvc database wordpress string postgresql wpf windows xcode bash git oracle list vb.net multithreading eclipse algorithm macos powershell visual-studio image forms numpy scala function api selenium