Parse XML data from a single cell/field

**Dormilich** · Nov 6 '14, 11:02 AM

do you need to process that XML file manually? because any automated task (script, programme, etc.) will easily handle one row xml files.

**DXCampbell** · Nov 6 '14, 01:03 PM

I would prefer to handle it in an automated fashion. below is a sample record:

<?xml version="1.0" encoding="utf-8"?> <ShelbyDonation Tran xmlns:xsi="http ://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http ://www.w3.org/2001/XMLSchema" xmlns="urn:blac kbaud.RE7.XDATA "> <Origin> <PageName>DXC-Test XML</PageName> <PageID>9678</PageID> <PartName>DXC - XML</PartName> <PartID>18756 </PartID> <PartTypeID>4 5</PartTypeID> <AppealID>0</AppealID> <PageURL>http ://pssandbox1.blac kbaud.com/netcommunity/old/testxml</PageURL> <AdminPartURL>h ttp://pssandbox1.blac kbaud.com/netcommunity/old/cms/contenthome/id/18756?cid=18756 </AdminPartURL> <TransactionDat e>2014-11-05T19:45:33.057 6172Z</TransactionDate > <RecordedByUser DisplayName>Don ald X. Campbell</RecordedByUserD isplayName> <RecordedByUser Name>DonaldCa</RecordedByUserN ame> <RecordedByUser ID>515</RecordedByUserI D> <TransactionVer sion>6.58.806.0 </TransactionVers ion> <ClientSitesID> 1</ClientSitesID> </Origin> <PKID>1337</PKID> <TransactionGUI D>8502d574-578f-472f-bb2c-7a29e2db32c7</TransactionGUID > <ShelbyDonation ID>687</ShelbyDonationI D> <ShelbyDesignat ionID>0</ShelbyDesignati onID> <Donor> <Title>Mr.</Title> <FirstName>Dona ld</FirstName> <LastName>Campb ell</LastName> <ConstituentCod eID>0</ConstituentCode ID> <BackOfficeID>1 495</BackOfficeID> <CompanyRelatio nID>0</CompanyRelation ID> <CompanyRecordI D>0</CompanyRecordID > <Phone>55555512 12</Phone> <EmailAddress>D onald.Campbell@ blackbaud.com</EmailAddress> <PhoneTypes> <Personal>Hom e</Personal> <Business>Busin ess</Business> <Email>Preferre d Email</Email> </PhoneTypes> <Address> <StreetAddress> 2000 Daniel Island Drive</StreetAddress> <City>Charlesto n</City> <State>SC</State> <ZIP>29492</ZIP> <Country>Unit ed States</Country> </Address> <RemoveSpouse>f alse</RemoveSpouse> <ClientUsersID> 0</ClientUsersID> </Donor> <Gift> <BackOfficeID>0 </BackOfficeID> <Amount>500</Amount> <IsAthon>fals e</IsAthon> <FinderNum>-1</FinderNum> <GiftDate>201 4-11-05T19:45:33.081 0547Z</GiftDate> <FundID>0</FundID> <PaymentMethod> CreditCard</PaymentMethod> <CreditType>Vis a</CreditType> <CardHolderName >Donald X. Campbell</CardHolderName> <AuthorizationC ode>00000</AuthorizationCo de> <ReferenceNumbe r /> <CreditCardNumb er>************ 1111</CreditCardNumbe r> <ExpirationMont h>1</ExpirationMonth > <ExpirationYear >2015</ExpirationYear> <Anonymous>fals e</Anonymous> <Corporate>fals e</Corporate> <GiftAid>fals e</GiftAid> <Tribute> <Name>My Nanna</Name> <Description>Li fe</Description> <TributeType>Fo r the Benefit of</TributeType> <TributeID>0</TributeID> <TributeRecordI D>0</TributeRecordID > <TributeID_Guid >00000000-0000-0000-0000-000000000000</TributeID_Guid> </Tribute> <IsTruePledge>f alse</IsTruePledge> <TruePledgeNumb erOfInstallment s>0</TruePledgeNumbe rOfInstallments > <TruePledgeInst allmentAmount>0 </TruePledgeInsta llmentAmount> <TruePledgeLast InstallmentAmou nt>0</TruePledgeLastI nstallmentAmoun t> <IsPaymentTowar dsExistingPledg e>false</IsPaymentToward sExistingPledge > <IsLastPaymentT owardsExistingP ledge>false</IsLastPaymentTo wardsExistingPl edge> <ExistingPledge OriginalAmt>0</ExistingPledgeO riginalAmt> <ExistingPledge OriginalDate>00 01-01-01T00:00:00</ExistingPledgeO riginalDate> <ExistingPledge CurrentBalance> 0</ExistingPledgeC urrentBalance> <ExistingPledge DueNowAmt>0</ExistingPledgeD ueNowAmt> <ExistingPledge DueNowDate>0001-01-01T00:00:00</ExistingPledgeD ueNowDate> <ExistingPledge NextInstallAmt> 0</ExistingPledgeN extInstallAmt> <ExistingPledge NextInstallDate >0001-01-01T00:00:00</ExistingPledgeN extInstallDate> <CurrencyType>U SD</CurrencyType> <ISO4217>USD</ISO4217> <MGCompany> <MGCompanyInfor mation> <FromMFO>fals e</FromMFO> <CompanyName> X Company</CompanyName> <DateLastUpdate d>0001-01-01T00:00:00</DateLastUpdated > <SpecialTermsRe strictions>fals e</SpecialTermsRes trictions> <SpecialTermsEl igibility>false </SpecialTermsEli gibility> <MinimumGift> 0</MinimumGift> <MaximumPerGift >0</MaximumPerGift> <DonorAnnualMax imum>0</DonorAnnualMaxi mum> <LifetimeTotal> 0</LifetimeTotal> <MatchRatio>0 </MatchRatio> <SpecialTermsRu les>false</SpecialTermsRul es> <System_Record_ ID>0</System_Record_I D> <PluginUserCrea ted>false</PluginUserCreat ed> </MGCompanyInform ation> </MGCompany> <Comments /> <TripleDES192>t rue</TripleDES192> <Attributes> <AttributeInfor mation> <AttributeTypeI D>208</AttributeTypeID > <Data /> </AttributeInform ation> </Attributes> <Designations > <DesignationInf ormation> <ID>1093</ID> <Amount>500.0 0</Amount> <Description>Un restricted</Description> <BackOfficeID>5 3</BackOfficeID> <BackOfficeID_G uid>00000000-0000-0000-0000-000000000000</BackOfficeID_Gu id> <GivingLevelID> 0</GivingLevelID> </DesignationInfo rmation> </Designations> <MarkGiftAsRece ipted>false</MarkGiftAsRecei pted> <eReceiptNumber >0</eReceiptNumber> <IsPaymentTowar dsExistingRecur ringGift>false</IsPaymentToward sExistingRecurr ingGift> <CurrencySymbol >$</CurrencySymbol> </Gift> <EmailID>0</EmailID> <ProcessedDate> 0001-01-01T00:00:00</ProcessedDate> <DonationContex t>Other</DonationContext > <Status>Complet ed</Status> <FinderNum>0</FinderNum> </ShelbyDonationT ran>

**Dormilich** · Nov 6 '14, 01:33 PM

I would prefer to handle it in an automated fashion.

then there is no need to reformat the XML. XML parsers only look for tags, not for line-breaks.

**DXCampbell** · Nov 6 '14, 02:48 PM

I am new to the XML parsing world. I downloaded the XMLEditor from TakeAwayCode. Doesn't seem to handle the single line format. Is there a recommended tool/parser?

**Dormilich** · Nov 6 '14, 02:51 PM

I downloaded the XMLEditor from TakeAwayCode.

so you do want to work with the XML manually?

**DXCampbell** · Nov 6 '14, 03:07 PM

LOL. That is the self imposed limitation due to my newness. There will be hundreds of records that will need to be parsed. Doing it manually will be a recipe for disaster. I would much prefer a utility/script/tool to pass the XML file through and end up with an excel file of the results. Downstream there will be another tool that reads the spreadsheet for further processing. This tool does not have the ability to read XML.

**Luuk** · Nov 6 '14, 07:09 PM

Maybe you should find a (programming)to ol which is capable of reading XML like:
1) PHP (http://php.net/manual/en/book.simplexml.php)
2) Perl (http://goo.gl/3iTdVX)

and, of course, some programming skills ;-)

**DXCampbell** · Nov 6 '14, 09:24 PM

Thanks for your input