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?
Record ID | Advertising Charge1 | Advertising Charge2 |
1 | 416.4 | 624.6 |
Solved! Go to Solution.
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 😅
Cant tell where the spaces are but this regex will probably work:
AdvertisingCharge1" value="([\d\.]+).*AdvertisingCharge2" value="([\d\.]+)
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 & 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" 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"" msrp="390.00" invoice="354.90" /><option type="Other" code="RG1" desc="BLACKOUT PACKAGE: (DEALER INSTALLED) * 20" 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>
No worries @bryantv_autonation ! glad I could help 😄