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!
LVL 1
Dustin StanleyEntrepreneurAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

zc2Commented:
The difference between the original XML and the altered one that in the original there is a default namespace:
xmlns="urn:ebay:apis:eBLBaseComponents"

Open in new window

So, in the XSLT it has to be explicitly declared
xmlns:e="urn:ebay:apis:eBLBaseComponents"

Open in new window

and used, like the following:
<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="/">
        <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="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:OrderArray">
        <xsl:apply-templates select="@*|node()"/>
    </xsl:template>

</xsl:stylesheet>

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dustin StanleyEntrepreneurAuthor Commented:
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

0
zc2Commented:
Kind of. You are declaring an alias "e" for the namespace URI "urn:ebay:apis:eBLBaseComponents".
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Dustin StanleyEntrepreneurAuthor Commented:
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....
0
zc2Commented:
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.
0
Dustin StanleyEntrepreneurAuthor Commented:
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?
0
zc2Commented:
Basically you are saying that all the elements (Nodes) in the XML file belong to the Namespace "urn:ebay:apis:eBLBaseComponents".
Yes, in the case there is xmlns:e="urn:ebay:apis:eBLBaseComponents" attribute in the root element.
So "urn:ebay:apis:eBLBaseComponents" is the container and we are saying to look for OrderID inside the container called "urn:ebay:apis:eBLBaseComponents"???
Well, guess it could be said that way.
<OrderArray> Completely disappears
I did not touch the OrderArray related template in my XSLT example.
    <xsl:template match="e:OrderArray">
        <xsl:apply-templates select="@*|node()"/>
    </xsl:template>

Open in new window

It does not copy the current element and it does not create a new element explicitly, like the "Buyer" template does.
to copy:
    <xsl:template match="e:OrderArray">
        <xsl:copy>
        <xsl:apply-templates select="@*|node()"/>
        </xsl:copy>
    </xsl:template>

Open in new window

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

Open in new window

0
Dustin StanleyEntrepreneurAuthor Commented:
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

0
zc2Commented:
Looks fine.
1
Dustin StanleyEntrepreneurAuthor Commented:
Thank you for real! Thanks zc2!!
0
zc2Commented:
You are welcome!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.