Loading ShopiFy data into the database.
Introduction
In the previous post we discussed extracting data from ShopiFy. Once data is extracted usually there is need to load it into the database. The data is extracted in XML format. I personally never understood obsession with XML. It seems to me that a lot of time organizations follow current trends and forget about common sense.Consider following example:
<CustomerOrderMessage> <OrderNumber>1</OrderNumber> </CustomerOrderMessage>
The actual data here is just one byte the rest is of it just metatada. To process it we need more hardware which is good for hardware vendors. Some times XML is so complicated so you have to be a rocket scientist to understand it. That leads to errors and drives the cost up.
We have a wiki page dedicated to this topic: Advantages and disadvantages of working with XML
Simplicity is the answer.
Here is ShopiFy orders XML file example (part of it)
<?xml version="1.0" encoding="UTF-8"?>
<orders type="array">
<order>
<id type="integer">3961189377</id>
<email></email>
<closed-at type="dateTime" nil="true"/>
<created-at type="dateTime">2016-09-13T10:17:35-04:00</created-at>
<updated-at type="dateTime">2016-10-03T14:24:15-04:00</updated-at>
<number type="integer">4</number>
<note>order for team</note>
<token>8dfabd2504c936bb5da4bd00a406f6ba</token>
<gateway>bogus</gateway>
<test type="boolean">true</test>
The file is very complex and it has a hierarchical structure.
What we want to do is to convert it more readable format using XSLT
About XSLT:
XSLT (Extensible Stylesheet Language Transformations) is a language for transforming XML documents into other XML documents, or other objects such as HTML for web pages, plain text or into XSL Formatting Objects which can then be converted to PDF, PostScript and PNG.Typically, input documents are XML files, but anything from which the processor can build an XQuery and XPath Data Model can be used, for example relational database tables, or geographical information systems.
Source: Wikipedia
This article provides basic XSLT transformation example:
Extracting Order Items Data
XSLT:
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="xml" indent="yes" version="1.0"/>
<xsl:template match="orders">
<TABLE>
<xsl:for-each select="order/line-items/line-item">
<RECORD>
<orderid><xsl:value-of select="ancestor::order[1]/id"/></orderid>
<id><xsl:value-of select="id"/></id>
<variant-id><xsl:value-of select="variant-id"/></variant-id>
<title><xsl:value-of select="title"/></title>
<quantity><xsl:value-of select="quantity"/></quantity>
<price><xsl:value-of select="price"/></price>
<grams><xsl:value-of select="grams"/></grams>
<sku><xsl:value-of select="sku"/></sku>
<variant-title><xsl:value-of select="variant-title"/></variant-title>
<vendor><xsl:value-of select="vendor"/></vendor>
<fulfillment-service><xsl:value-of select="fulfillment-servicer"/></fulfillment-service>
<product-id><xsl:value-of select="product-id"/></product-id>
<requires-shipping><xsl:value-of select="requires-shipping"/></requires-shipping>
<taxable><xsl:value-of select="taxable"/></taxable>
<gift-card><xsl:value-of select="gift-card"/></gift-card>
<name><xsl:value-of select="name"/></name>
<variant-inventory-management><xsl:value-of select="variant-inventory-management"/></variant-inventory-management>
<product-exists><xsl:value-of select="product-exists"/></product-exists>
<fulfillable-quantity><xsl:value-of select="fulfillable-quantity"/></fulfillable-quantity>
<total-discount><xsl:value-of select="total-discount"/></total-discount>
<fulfillment-status><xsl:value-of select="fulfillment-status"/></fulfillment-status>
</RECORD>
</xsl:for-each>
</TABLE>
</xsl:template>
</xsl:stylesheet>
Resulting XML file:
<?xml version="1.0"?>
<TABLE>
<RECORD>
<orderid>3961189377</orderid>
<id>7065894081</id>
<variant-id>24726825089</variant-id>
<title>Short Sleeve T-Shirt</title>
<quantity>10</quantity>
<price>10.00</price>
<grams>680</grams>
<sku>Fill_power_tee</sku>
<variant-title></variant-title>
<vendor>Test1</vendor>
<fulfillment-service></fulfillment-service>
<product-id>7693092801</product-id>
<requires-shipping>true</requires-shipping>
<taxable>true</taxable>
<gift-card>false</gift-card>
<name>Short Sleeve T-Shirt</name>
<variant-inventory-management></variant-inventory-management>
<product-exists>true</product-exists>
<fulfillable-quantity>10</fulfillable-quantity>
<total-discount>0.00</total-discount>
<fulfillment-status></fulfillment-status>
</RECORD>
<RECORD>
<orderid>3961181825</orderid>
<id>7065882305</id>
<variant-id>24726825089</variant-id>
<title>Short Sleeve T-Shirt</title>
<quantity>1</quantity>
<price>10.00</price>
<grams>680</grams>
<sku>Fill_power_tee</sku>
<variant-title></variant-title>
<vendor>Test1</vendor>
<fulfillment-service></fulfillment-service>
<product-id>7693092801</product-id>
<requires-shipping>true</requires-shipping>
<taxable>true</taxable>
<gift-card>false</gift-card>
<name>Short Sleeve T-Shirt</name>
<variant-inventory-management></variant-inventory-management>
<product-exists>true</product-exists>
<fulfillable-quantity>0</fulfillable-quantity>
<total-discount>0.00</total-discount>
<fulfillment-status>fulfilled</fulfillment-status>
</RECORD>
</TABLE>
Table Creation Script
create table items (
[orderid] varchar (12),
[id] varchar (17),
[variant-id] varchar (21),
[title] varchar (26),
[quantity] varchar (8),
[price] varchar (9),
[grams] varchar (8),
[sku] varchar (33),
[variant-title] varchar (9),
[vendor] varchar (8),
[fulfillment-service] varchar (8),
[product-id] varchar (30),
[requires-shipping] varchar (9),
[taxable] varchar (8),
[gift-card] varchar (8),
[name] varchar (50),
[variant-inventory-management] varchar (8),
[product-exists] varchar (8),
[fulfillable-quantity] varchar (8),
[total-discount] varchar (9),
[fulfillment-status] varchar (40)
)
Extracting Orders Data
XSLT:
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="xml" indent="yes" version="1.0"/>
<xsl:template match="orders">
<TABLE>
<xsl:for-each select="order">
<RECORD>
<orderid><xsl:value-of select="id"/></orderid>
<email><xsl:value-of select="email"/></email>
<closed-at><xsl:value-of select="closed-at"/></closed-at>
<created-at><xsl:value-of select="created-at"/></created-at>
<updated-at><xsl:value-of select="updated-at"/></updated-at>
<number><xsl:value-of select="number"/></number>
<note><xsl:value-of select="note"/></note>
<token><xsl:value-of select="token"/></token>
<gateway><xsl:value-of select="gateway"/></gateway>
<test><xsl:value-of select="test"/></test>
<total-price><xsl:value-of select="total-price"/></total-price>
<subtotal-price><xsl:value-of select="subtotal-price"/></subtotal-price>
<total-weight><xsl:value-of select="total-weight"/></total-weight>
<total-tax><xsl:value-of select="total-tax"/></total-tax>
<taxes-included><xsl:value-of select="taxes-included"/></taxes-included>
<currency><xsl:value-of select="currency"/></currency>
<financial-status><xsl:value-of select="financial-status"/></financial-status>
<confirmed><xsl:value-of select="confirmed"/></confirmed>
<total-discounts><xsl:value-of select="total-discounts"/></total-discounts>
<total-line-items-price><xsl:value-of select="total-line-items-price"/></total-line-items-price>
<cart-token><xsl:value-of select="cart-token"/></cart-token>
<buyer-accepts-marketing><xsl:value-of select="buyer-accepts-marketing"/></buyer-accepts-marketing>
<name><xsl:value-of select="name"/></name>
<referring-site><xsl:value-of select="referring-site"/></referring-site>
<landing-site><xsl:value-of select="landing-site"/></landing-site>
<cancelled-at><xsl:value-of select="cancelled-at"/></cancelled-at>
<cancel-reason><xsl:value-of select="cancel-reason"/></cancel-reason>
<total-price-usd><xsl:value-of select="total-price-usd"/></total-price-usd>
<checkout-token><xsl:value-of select="checkout-token"/></checkout-token>
<reference><xsl:value-of select="reference"/></reference>
<user-id><xsl:value-of select="user-id"/></user-id>
<location-id><xsl:value-of select="location-id"/></location-id>
<source-identifier><xsl:value-of select="source-identifier"/></source-identifier>
<source-url><xsl:value-of select="source-url"/></source-url>
<processed-at><xsl:value-of select="processed-at"/></processed-at>
<device-id><xsl:value-of select="device-id"/></device-id>
<browser-ip><xsl:value-of select="browser-ip"/></browser-ip>
<landing-site-ref><xsl:value-of select="landing-site-ref"/></landing-site-ref>
<order-number><xsl:value-of select="order-number"/></order-number>
<processing-method><xsl:value-of select="processing-method"/></processing-method>
<checkout-id><xsl:value-of select="checkout-id"/></checkout-id>
<source-name><xsl:value-of select="source-name"/></source-name>
<fulfillment-status><xsl:value-of select="fulfillment-status"/></fulfillment-status>
<tags><xsl:value-of select="tags"/></tags>
<contact-email><xsl:value-of select="contact-email"/></contact-email>
<order-status-url><xsl:value-of select="order-status-url"/></order-status-url>
<client-details-browser-ip><xsl:value-of select="client-details/browser-ip"/></client-details-browser-ip>
<client-details-accept-language><xsl:value-of select="client-details/accept-language"/></client-details-accept-language>
<client-details-user-agent><xsl:value-of select="client-details/user-agent"/></client-details-user-agent>
<client-details-session-hash><xsl:value-of select="client-details/session-hash"/></client-details-session-hash>
<client-details-browser-width><xsl:value-of select="client-details/browser-width"/></client-details-browser-width>
<client-details-browser-height><xsl:value-of select="client-details/browser-width"/></client-details-browser-height>
<payment-details-credit-card-bin><xsl:value-of select="payment-details/credit-card-bin"/></payment-details-credit-card-bin>
<payment-details-avs-result-code><xsl:value-of select="payment-details/avs-result-code"/></payment-details-avs-result-code>
<payment-details-cvv-result-code><xsl:value-of select="payment-details/cvv-result-code"/></payment-details-cvv-result-code>
<payment-details-credit-card-number><xsl:value-of select="payment-details/credit-card-number"/></payment-details-credit-card-number>
<payment-details-credit-card-company><xsl:value-of select="payment-details/credit-card-company"/></payment-details-credit-card-company>
<customer-id><xsl:value-of select="customer/id"/></customer-id>
<customer-email><xsl:value-of select="customer/email"/></customer-email>
<customer-accepts-marketing><xsl:value-of select="customer/accepts-marketing"/></customer-accepts-marketing>
<customer-created-at><xsl:value-of select="customer/created-at"/></customer-created-at>
<customer-updated-at><xsl:value-of select="customer/updated-at"/></customer-updated-at>
<customer-first-name><xsl:value-of select="customer/first-name"/></customer-first-name>
<customer-last-name><xsl:value-of select="customer/last-name"/></customer-last-name>
<customer-orders-count><xsl:value-of select="customer/orders-count"/></customer-orders-count>
<customer-state><xsl:value-of select="customer/state"/></customer-state>
<customer-total-spent><xsl:value-of select="customer/total-spent"/></customer-total-spent>
<customer-last-order-id><xsl:value-of select="customer/last-order-id"/></customer-last-order-id>
<customer-note><xsl:value-of select="customer/note"/></customer-note>
<customer-verified-email><xsl:value-of select="customer/verified-email"/></customer-verified-email>
<customer-multipass-identifier><xsl:value-of select="customer/multipass-identifier"/></customer-multipass-identifier>
<customer-tax-exempt><xsl:value-of select="customer/tax-exempt"/></customer-tax-exempt>
<customer-tags><xsl:value-of select="customer/tags"/></customer-tags>
<customer-last-order-name><xsl:value-of select="customer/last-order-name"/></customer-last-order-name>
<customer-default-address-id><xsl:value-of select="customer/default-address/id"/></customer-default-address-id>
<customer-default-address-first-name><xsl:value-of select="customer/default-address/first-name"/></customer-default-address-first-name>
<customer-default-address-last-name><xsl:value-of select="customer/default-address/first-name"/></customer-default-address-last-name>
<customer-default-address-company><xsl:value-of select="customer/default-address/company"/></customer-default-address-company>
<customer-default-address-address1><xsl:value-of select="customer/default-address/address1"/></customer-default-address-address1>
<customer-default-address-address2><xsl:value-of select="customer/default-address/address2"/></customer-default-address-address2>
<customer-default-address-city><xsl:value-of select="customer/default-address/city"/></customer-default-address-city>
<customer-default-address-province><xsl:value-of select="customer/default-address/province"/></customer-default-address-province>
<customer-default-address-country><xsl:value-of select="customer/default-address/country"/></customer-default-address-country>
<customer-default-address-zip><xsl:value-of select="customer/default-address/zip"/></customer-default-address-zip>
<customer-default-address-phone><xsl:value-of select="customer/default-address/phone"/></customer-default-address-phone>
<customer-default-address-name><xsl:value-of select="customer/default-address/name"/></customer-default-address-name>
<customer-default-address-province-code><xsl:value-of select="customer/default-address/province-code"/></customer-default-address-province-code>
<customer-default-address-country-code><xsl:value-of select="customer/default-address/country-code"/></customer-default-address-country-code>
<customer-default-address-country-name><xsl:value-of select="customer/default-address/country-name"/></customer-default-address-country-name>
<customer-default-address-default><xsl:value-of select="customer/default-address/default"/></customer-default-address-default>
</RECORD>
</xsl:for-each>
</TABLE>
</xsl:template>
</xsl:stylesheet>
Table Creation Script
create table orders_list (
[orderid] varchar (10),
[email] varchar (17),
[closed-at] varchar (25),
[created-at] varchar (25),
[updated-at] varchar (25),
[number] varchar (8),
[note] varchar (200),
[token] varchar (100),
[gateway] varchar (9),
[test] varchar (6),
[total-price] varchar (13),
[subtotal-price] varchar (16),
[total-weight] varchar (14),
[total-tax] varchar (10),
[taxes-included] varchar (16),
[currency] varchar (10),
[financial-status] varchar (18),
[confirmed] varchar (11),
[total-discounts] varchar (17),
[total-line-items-price] varchar (24),
[cart-token] varchar (100),
[buyer-accepts-marketing] varchar (25),
[name] varchar (6),
[referring-site] varchar (55),
[landing-site] varchar (29),
[cancelled-at] varchar (14),
[cancel-reason] varchar (15),
[total-price-usd] varchar (16),
[checkout-token] varchar (100),
[reference] varchar (11),
[user-id] varchar (9),
[location-id] varchar (13),
[source-identifier] varchar (19),
[source-url] varchar (12),
[processed-at] varchar (25),
[device-id] varchar (11),
[browser-ip] varchar (13),
[landing-site-ref] varchar (18),
[order-number] varchar (14),
[processing-method] varchar (19),
[checkout-id] varchar (13),
[source-name] varchar (100),
[fulfillment-status] varchar (20),
[tags] varchar (13),
[contact-email] varchar (17),
[order-status-url] varchar (133),
[client-details-browser-ip] varchar (27),
[client-details-accept-language] varchar (32),
[client-details-user-agent] varchar (255),
[client-details-session-hash] varchar (32),
[client-details-browser-width] varchar (30),
[client-details-browser-height] varchar (31),
[payment-details-credit-card-bin] varchar (33),
[payment-details-avs-result-code] varchar (33),
[payment-details-cvv-result-code] varchar (33),
[payment-details-credit-card-number] varchar (35),
[payment-details-credit-card-company] varchar (37),
[customer-id] varchar (13),
[customer-email] varchar (17),
[customer-accepts-marketing] varchar (28),
[customer-created-at] varchar (25),
[customer-updated-at] varchar (25),
[customer-first-name] varchar (20),
[customer-last-name] varchar (20),
[customer-orders-count] varchar (23),
[customer-state] varchar (16),
[customer-total-spent] varchar (22),
[customer-last-order-id] varchar (24),
[customer-note] varchar (15),
[customer-verified-email] varchar (24),
[customer-multipass-identifier] varchar (31),
[customer-tax-exempt] varchar (21),
[customer-tags] varchar (15),
[customer-last-order-name] varchar (26),
[customer-default-address-id] varchar (29),
[customer-default-address-first-name] varchar (37),
[customer-default-address-last-name] varchar (35),
[customer-default-address-company] varchar (34),
[customer-default-address-address1] varchar (35),
[customer-default-address-address2] varchar (35),
[customer-default-address-city] varchar (31),
[customer-default-address-province] varchar (35),
[customer-default-address-country] varchar (34),
[customer-default-address-zip] varchar (29),
[customer-default-address-phone] varchar (32),
[customer-default-address-name] varchar (31),
[customer-default-address-province-code] varchar (40),
[customer-default-address-country-code] varchar (39),
[customer-default-address-country-name] varchar (39),
[customer-default-address-default] varchar (10)
)
Note: Please contacts us if you need assistance with data transformation. we will be happy to assist.
Loading data from XML
No comments:
Post a Comment