From XML to Datatable

Help Studio
, , , , ,
1 
<?xml version="1.0" encoding="UTF-8"?>
<GetSellerListResponse xmlns="urn:ebay:apis:eBLBaseComponents"><Timestamp>2024-03-21T07:23:26.362Z</Timestamp><Ack>Success</Ack><Version>1271</Version><Build>E1271_CORE_APISELLING_19187371_R1</Build><HardExpirationWarning>2024-03-21 14:50:04</HardExpirationWarning><PaginationResult><TotalNumberOfPages>279</TotalNumberOfPages><TotalNumberOfEntries>279</TotalNumberOfEntries></PaginationResult><HasMoreItems>true</HasMoreItems><ItemArray><Item><AutoPay>false</AutoPay><BuyerProtection>ItemIneligible</BuyerProtection><BuyItNowPrice currencyID="USD">0.0</BuyItNowPrice><Country>LT</Country><Currency>EUR</Currency><Description>&lt;font rwr=&quot;1&quot; style=&quot;&quot;&gt; &lt;p style=&quot;text-align: center; background: rgb(255, 255, 255); text-indent: 27pt; vertical-align: baseline;&quot;&gt;  &lt;font face=&quot;Arial&quot;&gt;   &lt;span style=&quot;font-size: 18.6667px;&quot;&gt;Artikel:    &lt;span style=&quot;white-space: pre;&quot;&gt; &lt;/span&gt;     &lt;b&gt;STOSSSTANGE&lt;/b&gt;    &lt;/span&gt;   &lt;/font&gt; &lt;/p&gt;&lt;p style=&quot;text-align: center; background: rgb(255, 255, 255); text-indent: 27pt; vertical-align: baseline;&quot;&gt; &lt;font face=&quot;Arial&quot;&gt;  &lt;span style=&quot;font-size: 18.6667px;&quot;&gt;Hersteller:   &lt;span style=&quot;white-space:pre&quot;&gt; &lt;/span&gt;    &lt;b&gt;unbranded&lt;/b&gt;   &lt;/span&gt; &lt;/font&gt;&lt;/p&gt;&lt;p style=&quot;text-align: center; background: rgb(255, 255, 255); text-indent: 27pt; vertical-align: baseline;&quot;&gt; &lt;font face=&quot;Arial&quot;&gt;  &lt;span style=&quot;font-size: 18.6667px;&quot;&gt;OEM Nummer:   &lt;span style=&quot;white-space:pre&quot;&gt; &lt;/span&gt;    &lt;b&gt;86511-2Y000&lt;/b&gt;   &lt;/span&gt; &lt;/font&gt;&lt;/p&gt;&lt;p style=&quot;text-align: center; background: rgb(255, 255, 255); text-indent: 27pt; vertical-align: baseline;&quot;&gt; &lt;font face=&quot;Arial&quot;&gt;  &lt;span style=&quot;font-size: 18.6667px;&quot;&gt;Farbe   &lt;span style=&quot;white-space:pre&quot;&gt; &lt;/span&gt;    &lt;b&gt;Grau, Grundiert&lt;/b&gt;  &lt;/span&gt; &lt;/font&gt;&lt;/p&gt;&lt;p style=&quot;text-align: center; background: rgb(255, 255, 255); text-indent: 27pt; vertical-align: baseline;&quot;&gt; &lt;font face=&quot;Arial&quot;&gt;  &lt;span style=&quot;font-size: 18.6667px;&quot;&gt;Einbauseite   &lt;span style=&quot;white-space: pre;&quot;&gt; &lt;/span&gt;    &lt;b&gt; - &lt;/b&gt;  &lt;/span&gt; &lt;/font&gt;&lt;/p&gt;&lt;/font&gt;</Description><ItemID>354495397177</ItemID><ListingDetails><Adult>false</Adult><BindingAuction>false</BindingAuction><CheckoutEnabled>true</CheckoutEnabled><ConvertedBuyItNowPrice currencyID="USD">0.0</ConvertedBuyItNowPrice><ConvertedStartPrice currencyID="EUR">139.7</ConvertedStartPrice><ConvertedReservePrice currencyID="EUR">0.0</ConvertedReservePrice><HasReservePrice>false</HasReservePrice><StartTime>2023-01-04T09:43:30.000Z</StartTime><EndTime>2024-04-04T08:43:30.000Z</EndTime><ViewItemURL>https://www.ebay.de/itm/Stossfaenger-Stossstange-vorne-HYUNDAI-TUCSON-2010-2015-865112Y000-/354495397177</ViewItemURL><HasUnansweredQuestions>false</HasUnansweredQuestions><HasPublicMessages>false</HasPublicMessages><ViewItemURLForNaturalSearch>https://www.ebay.de/itm/Stossfaenger-Stossstange-vorne-HYUNDAI-TUCSON-2010-2015-865112Y000-/354495397177</ViewItemURLForNaturalSearch></ListingDetails><ListingDesigner><LayoutID>7710000</LayoutID><ThemeID>7710</ThemeID></ListingDesigner><ListingDuration>GTC</ListingDuration><ListingType>FixedPriceItem</ListingType><Location>Kaunas</Location><PrimaryCategory><CategoryID>33640</CategoryID><CategoryName>Auto &amp; Motorrad: Teile:Autoteile &amp; Zubehör:Karosserie-, Anbauteile &amp; Zubehör:Stoßstangen &amp; Teile:Stoßstangen &amp; -verstĂ€rker</CategoryName></PrimaryCategory><PrivateListing>false</PrivateListing><Quantity>2</Quantity><IsItemEMSEligible>false</IsItemEMSEligible><ReservePrice currencyID="EUR">0.0</ReservePrice><ReviseStatus><ItemRevised>true</ItemRevised></ReviseStatus><SellingStatus><BidCount>0</BidCount><BidIncrement currencyID="EUR">0.0</BidIncrement><ConvertedCurrentPrice currencyID="EUR">139.7</ConvertedCurrentPrice><CurrentPrice currencyID="EUR">139.7</CurrentPrice><LeadCount>0</LeadCount><MinimumToBid currencyID="EUR">139.7</MinimumToBid><QuantitySold>0</QuantitySold><ReserveMet>true</ReserveMet><SecondChanceEligible>false</SecondChanceEligible><ListingStatus>Active</ListingStatus></SellingStatus><ShippingDetails><ApplyShippingDiscount>false</ApplyShippingDiscount><CalculatedShippingRate><WeightMajor measurementSystem="Metric" unit="kg">0</WeightMajor><WeightMinor measurementSystem="Metric" unit="gm">0</WeightMinor></CalculatedShippingRate><SalesTax><SalesTaxPercent>0.0</SalesTaxPercent><ShippingIncludedInTax>false</ShippingIncludedInTax></SalesTax><ShippingServiceOptions><ShippingService>DE_StandardversandAusDemAusland</ShippingService><ShippingServiceCost currencyID="EUR">0.0</ShippingServiceCost><ShippingServiceAdditionalCost currencyID="EUR">0.0</ShippingServiceAdditionalCost><ShippingServicePriority>1</ShippingServicePriority><ExpeditedService>false</ExpeditedService><ShippingTimeMin>4</ShippingTimeMin><ShippingTimeMax>10</ShippingTimeMax><FreeShipping>true</FreeShipping></ShippingServiceOptions><ShippingServiceOptions><ShippingService>DE_DPBuecherWarensendung</ShippingService><ShippingServiceCost currencyID="EUR">29.0</ShippingServiceCost><ShippingServiceAdditionalCost currencyID="EUR">29.0</ShippingServiceAdditionalCost><ShippingServicePriority>2</ShippingServicePriority><ExpeditedService>false</ExpeditedService><ShippingTimeMin>2</ShippingTimeMin><ShippingTimeMax>4</ShippingTimeMax></ShippingServiceOptions><InternationalShippingServiceOption><ShippingService>DE_PaketInternational</ShippingService><ShippingServiceCost currencyID="EUR">59.0</ShippingServiceCost><ShippingServiceAdditionalCost currencyID="EUR">59.0</ShippingServiceAdditionalCost><ShippingServicePriority>1</ShippingServicePriority><ShipToLocation>Worldwide</ShipToLocation></InternationalShippingServiceOption><ShippingType>Flat</ShippingType><ThirdPartyCheckout>false</ThirdPartyCheckout><ShippingDiscountProfileID>0</ShippingDiscountProfileID><InternationalShippingDiscountProfileID>0</InternationalShippingDiscountProfileID><ExcludeShipToLocation>Ostfriesische Inseln</ExcludeShipToLocation><ExcludeShipToLocation>Nordfriesische Inseln</ExcludeShipToLocation><ExcludeShipToLocation>Hiddensee</ExcludeShipToLocation><ExcludeShipToLocation>Africa</ExcludeShipToLocation><ExcludeShipToLocation>Asia</ExcludeShipToLocation><ExcludeShipToLocation>Central America and Caribbean</ExcludeShipToLocation><ExcludeShipToLocation>Middle East</ExcludeShipToLocation><ExcludeShipToLocation>North America</ExcludeShipToLocation><ExcludeShipToLocation>Oceania</ExcludeShipToLocation><ExcludeShipToLocation>Southeast Asia</ExcludeShipToLocation><ExcludeShipToLocation>South America</ExcludeShipToLocation><ExcludeShipToLocation>AL</ExcludeShipToLocation><ExcludeShipToLocation>AD</ExcludeShipToLocation><ExcludeShipToLocation>BE</ExcludeShipToLocation><ExcludeShipToLocation>BA</ExcludeShipToLocation><ExcludeShipToLocation>BG</ExcludeShipToLocation><ExcludeShipToLocation>EE</ExcludeShipToLocation><ExcludeShipToLocation>FR</ExcludeShipToLocation><ExcludeShipToLocation>GI</ExcludeShipToLocation><ExcludeShipToLocation>GR</ExcludeShipToLocation><ExcludeShipToLocation>GG</ExcludeShipToLocation><ExcludeShipToLocation>IS</ExcludeShipToLocation><ExcludeShipToLocation>IT</ExcludeShipToLocation><ExcludeShipToLocation>JE</ExcludeShipToLocation><ExcludeShipToLocation>HR</ExcludeShipToLocation><ExcludeShipToLocation>LV</ExcludeShipToLocation><ExcludeShipToLocation>LI</ExcludeShipToLocation><ExcludeShipToLocation>LT</ExcludeShipToLocation><ExcludeShipToLocation>LU</ExcludeShipToLocation><ExcludeShipToLocation>MT</ExcludeShipToLocation><ExcludeShipToLocation>MK</ExcludeShipToLocation><ExcludeShipToLocation>MD</ExcludeShipToLocation><ExcludeShipToLocation>MC</ExcludeShipToLocation><ExcludeShipToLocation>ME</ExcludeShipToLocation><ExcludeShipToLocation>NO</ExcludeShipToLocation><ExcludeShipToLocation>PL</ExcludeShipToLocation><ExcludeShipToLocation>PT</ExcludeShipToLocation><ExcludeShipToLocation>RO</ExcludeShipToLocation><ExcludeShipToLocation>SM</ExcludeShipToLocation><ExcludeShipToLocation>CH</ExcludeShipToLocation><ExcludeShipToLocation>RS</ExcludeShipToLocation><ExcludeShipToLocation>SK</ExcludeShipToLocation><ExcludeShipToLocation>SI</ExcludeShipToLocation><ExcludeShipToLocation>ES</ExcludeShipToLocation><ExcludeShipToLocation>SJ</ExcludeShipToLocation><ExcludeShipToLocation>CZ</ExcludeShipToLocation><ExcludeShipToLocation>UA</ExcludeShipToLocation><ExcludeShipToLocation>HU</ExcludeShipToLocation><ExcludeShipToLocation>VA</ExcludeShipToLocation><ExcludeShipToLocation>BY</ExcludeShipToLocation><ExcludeShipToLocation>CY</ExcludeShipToLocation><ExcludeShipToLocation>AT</ExcludeShipToLocation><ExcludeShipToLocation>RU</ExcludeShipToLocation><ExcludeShipToLocation>PO Box</ExcludeShipToLocation><ExcludeShipToLocation>Packstation</ExcludeShipToLocation><SellerExcludeShipToLocationsPreference>false</SellerExcludeShipToLocationsPreference><RateTableDetails><InternationalRateTableId>5194863010</InternationalRateTableId></RateTableDetails></ShippingDetails><ShipToLocations>Worldwide</ShipToLocations><Site>Germany</Site><StartPrice currencyID="EUR">139.7</StartPrice><Storefront><StoreCategoryID>1</StoreCategoryID><StoreCategory2ID>0</StoreCategory2ID><StoreURL>https://www.ebay.de/str/partsshop4</StoreURL></Storefront><TimeLeft>P14DT1H20M4S</TimeLeft><Title>StoßfĂ€nger Stoßstange vorne fĂŒr HYUNDAI TUCSON 2010 - 2015 865112Y000</Title><UUID>EAB4D1A97B95443AB4C0BD7D0B36B9AB</UUID><GetItFast>false</GetItFast><SKU>PHN04103BB</SKU><PictureDetails><GalleryType>Gallery</GalleryType><PhotoDisplay>PicturePack</PhotoDisplay><PictureURL>https://i.ebayimg.com/00/s/MTM5MVgxNjAw/z/0B0AAOSw4-djtUpF/$_1.JPG?set_id=880000500F</PictureURL><PictureURL>https://i.ebayimg.com/00/s/MTQ5NFgxNjAw/z/UQsAAOSwl9ljtUpF/$_1.JPG?set_id=880000500F</PictureURL><PictureURL>https://i.ebayimg.com/00/s/MTQwMVgxNjAw/z/jz4AAOSwk55jtUpF/$_1.JPG?set_id=880000500F</PictureURL><PictureURL>https://i.ebayimg.com/00/s/MTMyN1gxNjAw/z/3E4AAOSwgn1jtUpE/$_1.JPG?set_id=880000500F</PictureURL><PictureURL>https://i.ebayimg.com/00/s/MTMyN1gxNjAw/z/QzgAAOSwWHpjtUpF/$_1.JPG?set_id=880000500F</PictureURL><PictureURL>https://i.ebayimg.com/00/s/MTMyN1gxNjAw/z/zrQAAOSwWWtjtUpF/$_1.JPG?set_id=880000500F</PictureURL></PictureDetails><DispatchTimeMax>3</DispatchTimeMax><ProxyItem>false</ProxyItem><BuyerGuaranteePrice currencyID="EUR">20000.0</BuyerGuaranteePrice><BuyerRequirementDetails><ShipToRegistrationCountry>true</ShipToRegistrationCountry></BuyerRequirementDetails><ReturnPolicy><ReturnsWithinOption>Days_30</ReturnsWithinOption><ReturnsWithin>30 Tage</ReturnsWithin><ReturnsAcceptedOption>ReturnsAccepted</ReturnsAcceptedOption><ReturnsAccepted>Verbraucher haben das Recht, den Artikel unter den angegebenen Bedingungen zurĂŒckzugeben.</ReturnsAccepted><ShippingCostPaidByOption>Seller</ShippingCostPaidByOption><ShippingCostPaidBy>VerkĂ€ufer trĂ€gt die Kosten der RĂŒcksendung der Waren</ShippingCostPaidBy><InternationalReturnsAcceptedOption>ReturnsAccepted</InternationalReturnsAcceptedOption><InternationalReturnsWithinOption>Days_30</InternationalReturnsWithinOption><InternationalShippingCostPaidByOption>Buyer</InternationalShippingCostPaidByOption></ReturnPolicy><ItemCompatibilityCount>63</ItemCompatibilityCount><ConditionID>1000</ConditionID><ConditionDisplayName>Neu</ConditionDisplayName><PostCheckoutExperienceEnabled>false</PostCheckoutExperienceEnabled><SellerProfiles><SellerShippingProfile><ShippingProfileID>229676965017</ShippingProfileID><ShippingProfileName>DIDELES FREE</ShippingProfileName></SellerShippingProfile><SellerReturnProfile><ReturnProfileID>220817326017</ReturnProfileID><ReturnProfileName>Standard return</ReturnProfileName></SellerReturnProfile><SellerPaymentProfile><PaymentProfileID>220171935017</PaymentProfileID><PaymentProfileName>SIGNEDA PAYMENT</PaymentProfileName></SellerPaymentProfile></SellerProfiles><ShippingPackageDetails><ShippingIrregular>false</ShippingIrregular><ShippingPackage>None</ShippingPackage><WeightMajor measurementSystem="Metric" unit="kg">0</WeightMajor><WeightMinor measurementSystem="Metric" unit="gm">0</WeightMinor></ShippingPackageDetails><HideFromSearch>false</HideFromSearch><eBayPlus>false</eBayPlus><eBayPlusEligible>false</eBayPlusEligible><IsSecureDescription>true</IsSecureDescription></Item></ItemArray><ItemsPerPage>1</ItemsPerPage><PageNumber>1</PageNumber><ReturnedItemCountActual>1</ReturnedItemCountActual><Seller><AboutMePage>false</AboutMePage><Email>teilehub22@gmail.com</Email><FeedbackScore>483</FeedbackScore><PositiveFeedbackPercent>98.8</PositiveFeedbackPercent><FeedbackPrivate>false</FeedbackPrivate><IDVerified>false</IDVerified><eBayGoodStanding>true</eBayGoodStanding><NewUser>false</NewUser><RegistrationDate>2022-06-03T10:50:19.000Z</RegistrationDate><Site>Germany</Site><Status>Confirmed</Status><UserID>partsshop4</UserID><UserIDChanged>false</UserIDChanged><VATStatus>VATExempt</VATStatus><SellerInfo><AllowPaymentEdit>true</AllowPaymentEdit><CheckoutEnabled>true</CheckoutEnabled><CIPBankAccountStored>false</CIPBankAccountStored><GoodStanding>true</GoodStanding><LiveAuctionAuthorized>false</LiveAuctionAuthorized><MerchandizingPref>OptIn</MerchandizingPref><QualifiesForB2BVAT>false</QualifiesForB2BVAT><StoreOwner>true</StoreOwner><StoreURL>https://www.ebay.de/str/partsshop4</StoreURL><SafePaymentExempt>false</SafePaymentExempt><TopRatedSeller>true</TopRatedSeller></SellerInfo><MotorsDealer>false</MotorsDealer></Seller></GetSellerListResponse>

Hello guys. I need help with the API call. I made a API call to ebay and got a answer. Now i need to form the answer into a datatable, but dont know how. I think its by using “Deserialize XML”, but what next? Any help would be appreciated. Thanks :slight_smile:

2

Hi,

Can you share expected result?

Regards,

1 Like
3

Hello @Yoichi , sure:

The best possible way would be to get the answer like this in the end:

image
image1902×737 25.2 KB

For every node that contains something inside of it, would be like this, lets take this as example:<LayoutID>7710000</LayoutID>
I would love for the LayoutID to be the header, and 7710000 would be below it, then so on and so on

4

With this call im getting information about my Ebay listings. Thoose will be expected to be imported into SQL table for further data manipulation, so I need it to be fixed up from XML to datatable

5

Hi Povilas,

I wrote a C# code to convert this response to a datatable:

	string xmlResponse = @in_XMLResponse;
    XmlDocument xmlDoc = new XmlDocument();
    xmlDoc.LoadXml(xmlResponse);

    XmlNamespaceManager nsManager = new XmlNamespaceManager(xmlDoc.NameTable);
    nsManager.AddNamespace("ebay", "urn:ebay:apis:eBLBaseComponents");

	dataTable = new DataTable();
    dataTable.Columns.Add("ItemID", typeof(string));
    dataTable.Columns.Add("Title", typeof(string));
    // You can add more columns as needed

    XmlNodeList itemNodes = xmlDoc.SelectNodes("//ebay:Item", nsManager);

    foreach (XmlNode itemNode in itemNodes)
    {
        string itemID = itemNode.SelectSingleNode("ebay:ItemID", nsManager)?.InnerText;
        string title = itemNode.SelectSingleNode("ebay:Title", nsManager)?.InnerText;
        dataTable.Rows.Add(itemID, title);
    }

image
image914×294 20.4 KB

You can add your desired columns to your SQL table in the code where I added the comment. You need to use ‘Invoke Code’ with the property set to C# and provide the necessary arguments.

I’m attaching the workflow; you can also check that out.
XMLToDataTable.zip (6.8 KB)

Best Regards,
Kardelen

1 Like
6

Thanks @kardelencihangir, ill check it out and let you know!

1 Like
7

HI,

Can you try the following sample?

image
image1023×1033 30 KB

strHead =String.Join(chr(9),xDoc.Descendants.Where(Function(x) x.HasElements = False).Select(Function(x) x.Name.LocalName))

strData =String.Join(chr(9),xDoc.Descendants.Where(Function(x) x.HasElements = False).Select(Function(x) x.Value))

Sample
Sample20240321-A (2).zip (9.3 KB)

Regards,

1 Like
8

Sure, thanks. Will try it out in a second! :slight_smile:

9

So I’ve tried both of the solutions given to me, and both of them work perfectly fine. Thanks a to these two legends - @Yoichi and @kardelencihangir, your help is highly appreciated! <3

1 Like
10

Very good news. Happy Automation :slight_smile:

Best Regards,
Kardelen

1 Like
11

Very good news. Happy Automation :slight_smile:

Best Regards,
Kardelen

1 Like
12

Im sorry, one more question. What if, there are multiple answers to the API call? What if I want to get multiple API answers with all the same columns in one go? How to modify it to paste out multiple answers?

13

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.