community Alteryx IO Alteryx.com
alteryx Community
Search
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Close
Free Trial

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Extracting data from XML text...

bryantv_autonation
6 - Meteoroid
‎06-09-2022 08:50 AM

I am trying to extract the numeric values highlighted in yellow from the xml text below.  I tried using the XML parse tool, but I keep getting invalid structure errors.  So, I think I would like to use some type of formula or Regex expression, but I'm struggling to figure it out.  The text below is a field in my dataset labeled "rawXml". I would like the output data to look like the table at the bottom.  Can anyone help me, please?

 

bryantv_autonation_0-1654789348344.png

 

Record IDAdvertising Charge1Advertising Charge2
1416.4624.6

 

Labels:
Reply

Accepted SolutionSolved! Go to Solution.

6 REPLIES 6
IraWatt
17 - Castor
17 - Castor
‎06-09-2022 10:36 AM

Hey @bryantv_autonation,

Can you copy the XML as text rather than a picture, that's a lot of text to write out into regex101: build, test, and debug regex 😅

Reply
IraWatt
17 - Castor
17 - Castor
‎06-09-2022 10:43 AM

Cant tell where the spaces are but this regex will probably work:

 

 

AdvertisingCharge1" value="([\d\.]+).*AdvertisingCharge2" value="([\d\.]+)

 

 

IraWatt_0-1654796588264.png

IraWatt_1-1654796651807.png

 

 

Reply
0 Likes
bryantv_autonation
6 - Meteoroid
‎06-09-2022 11:00 AM

Here is the text...I was trying not to share some of the private information by blurring the picture...I changed the information, so you can copy and paste it.

 

<invoice id="qgv66872" store="205914" make="Toyota" model="CUSTOM" model_nbr="CCz12598543" year="2022" vin="DJTRIFNVN30674GFGLSJ02958" weight="5803" wheelbase="" code="2" mesg="Cached"><attributes><attribute id="Dealer" value="35960-141" label="" /><attribute id="DealerName" value="Gulf Freeway" label="" /><attribute id="DealerAddr" value="123 anywhere" label="" /><attribute id="DealerCSZ" value="Kalimazoo, USA " label="" /><attribute id="InvoiceDate" value="2021-1-29" label="" /><attribute id="ExtractDate" value="2021-11-30" label="" /><attribute id="TotalHoldback" value="1249.20" label="" /><attribute id="BaseInvoice" value="37944.90" label="" /><attribute id="BaseMSRP" value="39900.00" label="" /><attribute id="TotalInvoice" value="42264.30" label="" /><attribute id="TotalMSRP" value="43335.00" label="" /><attribute id="DestinationCharge" value="1695.00" label="" /><attribute id="AdvertisingCharge1" value="416.40" label="" /><attribute id="AdvertisingCharge2" value="624.60" label="" /><attribute id="PlanPrice1" value="39942.17" label="EMP" /><attribute id="PlanPrice3" value="41523.30" label="SUP" /></attributes><options><option type="ColorExterior" code="G7C" desc="RED HOT" msrp="" invoice="" /><option type="ColorInterior" code="H0U" desc="JET BLACK" msrp="" invoice="" /><option type="Other" code="A2X" desc="10-WAY POWER DRIVER SEAT" msrp="290.00" invoice="263.90" /><option type="Other" code="C5U" desc="GVWR: 6,800 LBS. (3,084 KG)" msrp="" invoice="" /><option type="Other" code="FE9" desc="50-STATE EMISSIONS" msrp="" invoice="" /><option type="Other" code="GU6" desc="REAR AXLE: 3.42 RATIO" msrp="3.42" invoice="" /><option type="Other" code="G80" desc="AUTO LOCKING REAR DIFFERENTIAL" msrp="395.00" invoice="359.45" /><option type="Other" code="KI4" desc="120V INSTRUMENT PANEL &amp; CARGO BED POWER OUTLETS" msrp="225.00" invoice="204.75" /><option type="Other" code="L3B" desc="ENGINE: 2.7L TURBO" msrp="" invoice="" /><option type="Other" code="MQE" desc="TRANSMISSION: 8-SPEED AUTO" msrp="" invoice="" /><option type="Other" code="NZP" desc="20&quot; BRIGHT SILVER PAINTED ALUMINUM WHEELS" msrp="" invoice="" /><option type="Other" code="PCX" desc="CUSTOM CONVENIENCE PACKAGE" msrp="" invoice="" /><option type="Other" code="PDX" desc="CUSTOM VALUE PACKAGE" msrp="" invoice="" /><option type="Other" code="QAE" desc="ALL-TERRAIN TIRES" msrp="200.00" invoice="182.00" /><option type="Other" code="QK2" desc="MULTIFLEX TAILGATE" msrp="445.00" invoice="404.95" /><option type="Other" code="RGE" desc="SAFETY CONFIDENCE PACKAGE * FORWARD COLLISION ALERT * AUTOMATIC EMERGENCY BRAKING * FRONT PEDESTRIAN BRAKING * STEERING WHEEL AUDIO CONTROLS * DRIVER INFO CENTER, 4.2&quot;" msrp="390.00" invoice="354.90" /><option type="Other" code="RG1" desc="BLACKOUT PACKAGE: (DEALER INSTALLED) * 20&quot; HIGH GLOSS BLACK WHEELS * BLACK NAMEPLATES * BLACK BOWTIE" msrp="1595.00" invoice="1451.45" /><option type="Other" code="VTA" desc="BLACK CHROME EXHAUST TIPS (DEALER INSTALLED)" msrp="200.00" invoice="182.00" /><option type="Other" code="ZLA" desc="INFOTAINMENT PACKAGE" msrp="" invoice="" /><option type="Other" code="1SZ" desc="2.7L TURBO ENGINE CREDIT" msrp="2000.00-" invoice="1820.00-" /></options></invoice>

Reply
IraWatt
17 - Castor
17 - Castor
‎06-09-2022 11:04 AM

Hey @bryantv_autonation,

IraWatt_1-1654797847740.png

 

 

Workflow0506.yxmd
Reply
bryantv_autonation
6 - Meteoroid
‎06-09-2022 11:35 AM

@IraWatt

 

Thank you so much!  This is perfect!!  

Reply
IraWatt
17 - Castor
17 - Castor
‎06-09-2022 11:51 AM

No worries @bryantv_autonation ! glad I could help 😄

Reply
0 Likes
Labels