Link to home
Start Free TrialLog in
Avatar of Dustin Stanley
Dustin Stanley

asked on

I Need Help Understanding This Example Of XSLT Transforming XML In MS Access

I am looking over a Example of transforming a XML Document for importing into Access by using a XSLT File to transform it. Basically the the example is copying a node value from the Parent Node into several of the Children and Grandchildren Nodes so when you import into Access you can link the info back together by a Query. I have tried to apply my understanding (Not Much with XSLT) to my actual XML file and I have had some success but not accurately understanding it.

The actual example can be found here:
https://stackoverflow.com/questions/30029303/how-to-import-xml-with-nested-nodes-parent-child-relationships-into-access

I want to copy the <OrderID> Value to under my <Buyer> and <Item> Nodes.
In the example on the link above this would be the <C_NOT> Node.

My XSLT:
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:output indent="yes"/>
    <xsl:strip-space elements="*"/>

    <xsl:template match="/">
        <dataroot>
            <xsl:apply-templates select="@*|node()"/>
        </dataroot>
    </xsl:template>

    <xsl:template match="@*|node()">
        <xsl:copy>
            <xsl:apply-templates select="@*|node()"/>
        </xsl:copy>
    </xsl:template>

    <xsl:template match="OrderArray">
        <xsl:apply-templates select="@*|node()"/>
    </xsl:template>

	    <xsl:template match="Buyer">
        <Buyer>
            <OrderID><xsl:value-of select="../../../OrderID"/></OrderID>
            <xsl:apply-templates select="@*|node()"/>
        </Buyer>
    </xsl:template>

</xsl:stylesheet>

Open in new window


This XSLT works perfect with my Altered XML but Not with the Original XML. The Only thing i altered in the XML is I have removed  the First 11 Lines (1-11) and the last 4 Lines (95-98).

Original XML:
<?xml version="1.0" encoding="UTF-8"?>
<GetOrdersResponse xmlns="urn:ebay:apis:eBLBaseComponents">
	<Timestamp>2018-03-26T22:39:01.221Z</Timestamp>
	<Ack>Success</Ack>
	<Version>1051</Version>
	<Build>E1051_CORE_APIXO_18_R1</Build>
	<PaginationResult>
		<TotalNumberOfPages>1</TotalNumberOfPages>
		<TotalNumberOfEntries>56</TotalNumberOfEntries>
	</PaginationResult>
	<HasMoreOrders>false</HasMoreOrders>
	<OrderArray>
		<Order>
			<OrderID>240034646543654615</OrderID>
			<OrderStatus>Complete</OrderStatus>
			<AdjustmentAmount currencyID="USD">0.0</AdjustmentAmount>
			<AmountPaid currencyID="USD">0.0</AmountPaid>
			<AmountSaved currencyID="USD">0.0</AmountSaved>
			<CheckoutStatus>
				<eBayPaymentStatus></eBayPaymentStatus>
				<LastModifiedTime></LastModifiedTime>
				<PaymentMethod>None</PaymentMethod>
				<Status></Status>
				<IntegratedMerchantCreditCardEnabled>false</IntegratedMerchantCreditCardEnabled>
			</CheckoutStatus>
			<ShippingDetails>
				<SalesTax>
					<SalesTaxPercent></SalesTaxPercent>
					<SalesTaxState/>
					<ShippingIncludedInTax></ShippingIncludedInTax>
					<SalesTaxAmount currencyID="USD"></SalesTaxAmount>
				</SalesTax>
				<ShippingServiceOptions>
					<ShippingService>ShippingMethodStandard</ShippingService>
					<ShippingServiceCost currencyID="USD">0.0</ShippingServiceCost>
					<ShippingServicePriority>1</ShippingServicePriority>
					<ExpeditedService>false</ExpeditedService>
					<ShippingTimeMin>1</ShippingTimeMin>
					<ShippingTimeMax>5</ShippingTimeMax>
				</ShippingServiceOptions>
				<SellingManagerSalesRecordNumber>4037</SellingManagerSalesRecordNumber>
				<GetItFast>false</GetItFast>
			</ShippingDetails>
			<CreatingUserRole>Seller</CreatingUserRole>
			<CreatedTime>2018-02-22T00:21:12.000Z</CreatedTime>
			<PaymentMethods>PayPal</PaymentMethods>
			<SellerEmail>JoeSchmoe@gmail.com</SellerEmail>
			<ShippingAddress>
				<Name>JoeSchmoe</Name>
				<Street1>125 Blvd</Street1>
				<Street2/>
				<CityName>Shay</CityName>
				<StateOrProvince>MI</StateOrProvince>
				<Country>US</Country>
				<CountryName>United States</CountryName>
				<Phone>765 777 3333</Phone>
				<PostalCode>2345-2677</PostalCode>
				<AddressID>24545325t</AddressID>
				<AddressOwner>JoeSchmoe</AddressOwner>
				<ExternalAddressID/>
			</ShippingAddress>
			<ShippingServiceSelected>
				<ShippingService>ShippingMethodStandard</ShippingService>
				<ShippingServiceCost currencyID="USD">0.0</ShippingServiceCost>
			</ShippingServiceSelected>
			<Subtotal currencyID="USD">1.0</Subtotal>
			<Total currencyID="USD">1.0</Total>
			<TransactionArray>
				<Transaction>
					<Buyer>
						<Email>JoeSchmoe@gmail</Email>
						<UserFirstName>Joe</UserFirstName>
						<UserLastName>Schmoe</UserLastName>
					</Buyer>
					<ShippingDetails>
						<SellingManagerSalesRecordNumber>4035</SellingManagerSalesRecordNumber>
						<ShipmentTrackingDetails>
							<ShippingCarrierUsed>FedEx</ShippingCarrierUsed>
							<ShipmentTrackingNumber>2342435242423</ShipmentTrackingNumber>
						</ShipmentTrackingDetails>
					</ShippingDetails>
					<CreatedDate>2018-02-2200Z</CreatedDate>
					<Item>
						<ItemID>35344t654</ItemID>
						<Site>US</Site>
						<Title> HRC Class L</Title>
						<SKU>7HE40A0NSFGP</SKU>
						<ConditionID>1000</ConditionID>
						<ConditionDisplayName>New</ConditionDisplayName>
					</Item>
				</Transaction>
			</TransactionArray>
		</Order>
	</OrderArray>
	<OrdersPerPage>100</OrdersPerPage>
	<PageNumber>1</PageNumber>
	<ReturnedOrderCountActual>56</ReturnedOrderCountActual>
</GetOrdersResponse>

Open in new window



Altered XML:
	<OrderArray>
		<Order>
			<OrderID>240034646543654615</OrderID>
			<OrderStatus>Complete</OrderStatus>
			<AdjustmentAmount currencyID="USD">0.0</AdjustmentAmount>
			<AmountPaid currencyID="USD">0.0</AmountPaid>
			<AmountSaved currencyID="USD">0.0</AmountSaved>
			<CheckoutStatus>
				<eBayPaymentStatus></eBayPaymentStatus>
				<LastModifiedTime></LastModifiedTime>
				<PaymentMethod>None</PaymentMethod>
				<Status></Status>
				<IntegratedMerchantCreditCardEnabled>false</IntegratedMerchantCreditCardEnabled>
			</CheckoutStatus>
			<ShippingDetails>
				<SalesTax>
					<SalesTaxPercent></SalesTaxPercent>
					<SalesTaxState/>
					<ShippingIncludedInTax></ShippingIncludedInTax>
					<SalesTaxAmount currencyID="USD"></SalesTaxAmount>
				</SalesTax>
				<ShippingServiceOptions>
					<ShippingService>ShippingMethodStandard</ShippingService>
					<ShippingServiceCost currencyID="USD">0.0</ShippingServiceCost>
					<ShippingServicePriority>1</ShippingServicePriority>
					<ExpeditedService>false</ExpeditedService>
					<ShippingTimeMin>1</ShippingTimeMin>
					<ShippingTimeMax>5</ShippingTimeMax>
				</ShippingServiceOptions>
				<SellingManagerSalesRecordNumber>4037</SellingManagerSalesRecordNumber>
				<GetItFast>false</GetItFast>
			</ShippingDetails>
			<CreatingUserRole>Seller</CreatingUserRole>
			<CreatedTime>2018-02-22T00:21:12.000Z</CreatedTime>
			<PaymentMethods>PayPal</PaymentMethods>
			<SellerEmail>JoeSchmoe@gmail.com</SellerEmail>
			<ShippingAddress>
				<Name>JoeSchmoe</Name>
				<Street1>125 Blvd</Street1>
				<Street2/>
				<CityName>Shay</CityName>
				<StateOrProvince>MI</StateOrProvince>
				<Country>US</Country>
				<CountryName>United States</CountryName>
				<Phone>765 777 3333</Phone>
				<PostalCode>2345-2677</PostalCode>
				<AddressID>24545325t</AddressID>
				<AddressOwner>JoeSchmoe</AddressOwner>
				<ExternalAddressID/>
			</ShippingAddress>
			<ShippingServiceSelected>
				<ShippingService>ShippingMethodStandard</ShippingService>
				<ShippingServiceCost currencyID="USD">0.0</ShippingServiceCost>
			</ShippingServiceSelected>
			<Subtotal currencyID="USD">1.0</Subtotal>
			<Total currencyID="USD">1.0</Total>
			<TransactionArray>
				<Transaction>
					<Buyer>
						<Email>JoeSchmoe@gmail</Email>
						<UserFirstName>Joe</UserFirstName>
						<UserLastName>Schmoe</UserLastName>
					</Buyer>
					<ShippingDetails>
						<SellingManagerSalesRecordNumber>4035</SellingManagerSalesRecordNumber>
						<ShipmentTrackingDetails>
							<ShippingCarrierUsed>FedEx</ShippingCarrierUsed>
							<ShipmentTrackingNumber>2342435242423</ShipmentTrackingNumber>
						</ShipmentTrackingDetails>
					</ShippingDetails>
					<CreatedDate>2018-02-2200Z</CreatedDate>
					<Item>
						<ItemID>35344t654</ItemID>
						<Site>US</Site>
						<Title> HRC Class L</Title>
						<SKU>7HE40A0NSFGP</SKU>
						<ConditionID>1000</ConditionID>
						<ConditionDisplayName>New</ConditionDisplayName>
					</Item>
				</Transaction>
			</TransactionArray>
		</Order>
	</OrderArray>

Open in new window




What am I doing wrong with the XSLT that is not working with the Original XML File? Thank you for the help. XSLT is Very New To Me!
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
Avatar of Dustin Stanley
Dustin Stanley

ASKER

Thank you so much for the help. This is all alot to wrap my head around but I am trying.

So when you say:
xmlns:e="urn:ebay:apis:eBLBaseComponents"

Open in new window


Then I am declaring that:
e

Open in new window

Equals 
urn:ebay:apis:eBLBaseComponents

Open in new window

Avatar of zc2
zc2
Flag of United States of America image
Kind of. You are declaring an alias "e" for the namespace URI "urn:ebay:apis:eBLBaseComponents".
Avatar of Dustin Stanley
Dustin Stanley

ASKER

Sorry to be a pain but I am really wanting to understand this and I have been reading for days and trying to get this. I am WAY better with VBA.

So "urn:ebay:apis:eBLBaseComponents" is the "Default Namespace"  then it says:
<xsl:value-of select="ancestor::e:Order/e:OrderID"/>

Open in new window


is this saying to step down from "urn:ebay:apis:eBLBaseComponents"????

Like:
urn:ebay:apis:eBLBaseComponents:Order/urn:ebay:apis:eBLBaseComponents:OrderID

Open in new window


Sorry after your answer it threw me for a spin even more.

The way I am visualizing it is that I am stepping down through the XML form the root....
Avatar of zc2
zc2
Flag of United States of America image
I agree, this is somewhat confusing.
Even though xmlns:e="urn:ebay:apis:eBLBaseComponents" declares an alias "e", that does not mean the namespace URI string could be used as elements prefixes. So, this string would be invalid:
urn:ebay:apis:eBLBaseComponents:Order/urn:ebay:apis:eBLBaseComponents:OrderID

When an XML has "default namespace" (declared as xmlns="some unique string"), there no aliases needed, all elements belongs to that namespace (have implicit namespace prefixes if you wish). But to address those elements from XSLT (or locate them using XPath), we need to explicitly tell the processor in which the namespace we are searching in. Thus the same unique namespace URI has to be bound to an alias which is to be used as elements prefix.

Home my explanation is understandable.
Avatar of Dustin Stanley
Dustin Stanley

ASKER

Thanks a ton for the explanations as these help me a TON!  Ok forget coding and proper language for a second and tell me if I am correct plese.

Basically you are saying that all the elements (Nodes) in the XML file belong to the Namespace "urn:ebay:apis:eBLBaseComponents".

So "urn:ebay:apis:eBLBaseComponents" is the container and we are saying to look for OrderID inside the container called "urn:ebay:apis:eBLBaseComponents"???


By the way I am using Notepad++ and when I run this transformation my <OrderArray> Completely disappears. Why is that?
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
Avatar of Dustin Stanley
Dustin Stanley

ASKER

You are way more help than you probably know. Thank you so much!

I am going to run this by you just to confirm there is nothing my Amature Self has not noticed. In my example I truthfully had no idea what is going on just parts.

My mission is to just to keep everything the same but add OrderID to several Children such as <Buyer> and <Item>. I redid the Xslt and it seems to look ok but I want you to please confirm that this is correct. Thanks.
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
	xmlns:e="urn:ebay:apis:eBLBaseComponents">
    <xsl:output indent="yes"/>
    <xsl:strip-space elements="*"/>

   <xsl:template match="/">
        <xsl:copy>
        <xsl:apply-templates select="@*|node()"/>
        </xsl:copy>
    </xsl:template>

    <xsl:template match="@*|node()">
        <xsl:copy>
            <xsl:apply-templates select="@*|node()"/>
        </xsl:copy>
    </xsl:template>

	<xsl:template match="e:Buyer">
        <Buyer>
            <OrderID><xsl:value-of select="ancestor::e:Order/e:OrderID"/></OrderID>
            <xsl:apply-templates select="@*|node()"/>
        </Buyer>
    </xsl:template>
	<xsl:template match="e:Item">
        <Item>
            <OrderID><xsl:value-of select="ancestor::e:Order/e:OrderID"/></OrderID>
            <xsl:apply-templates select="@*|node()"/>
        </Item>
    </xsl:template>
</xsl:stylesheet>

Open in new window

Avatar of zc2
zc2
Flag of United States of America image
Looks fine.
Avatar of Dustin Stanley
Dustin Stanley

ASKER

Thank you for real! Thanks zc2!!
Avatar of zc2
zc2
Flag of United States of America image
You are welcome!