471,887 Members | 1,517 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,887 software developers and data experts.

Parse XML data from a single cell/field

I have a single field in a SQL 2008r2 database that contains XML data. The data is not parsed into separate rows, but in a single flat row. How can I parse out the XML data from this field and create a row based XML file?
Nov 5 '14 #1
8 3285
Dormilich
8,658 Expert Mod 8TB
do you need to process that XML file manually? because any automated task (script, programme, etc.) will easily handle one row xml files.
Nov 6 '14 #2
I would prefer to handle it in an automated fashion. below is a sample record:

<?xml version="1.0" encoding="utf-8"?> <ShelbyDonationTran xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="urn:blackbaud.RE7.XDATA"> <Origin> <PageName>DXC-Test XML</PageName> <PageID>9678</PageID> <PartName>DXC - XML</PartName> <PartID>18756</PartID> <PartTypeID>45</PartTypeID> <AppealID>0</AppealID> <PageURL>http://pssandbox1.blackbaud.com/netcommunity/old/testxml</PageURL> <AdminPartURL>http://pssandbox1.blackbaud.com/netcommunity/old/cms/contenthome/id/18756?cid=18756</AdminPartURL> <TransactionDate>2014-11-05T19:45:33.0576172Z</TransactionDate> <RecordedByUserDisplayName>Donald X. Campbell</RecordedByUserDisplayName> <RecordedByUserName>DonaldCa</RecordedByUserName> <RecordedByUserID>515</RecordedByUserID> <TransactionVersion>6.58.806.0</TransactionVersion> <ClientSitesID>1</ClientSitesID> </Origin> <PKID>1337</PKID> <TransactionGUID>8502d574-578f-472f-bb2c-7a29e2db32c7</TransactionGUID> <ShelbyDonationID>687</ShelbyDonationID> <ShelbyDesignationID>0</ShelbyDesignationID> <Donor> <Title>Mr.</Title> <FirstName>Donald</FirstName> <LastName>Campbell</LastName> <ConstituentCodeID>0</ConstituentCodeID> <BackOfficeID>1495</BackOfficeID> <CompanyRelationID>0</CompanyRelationID> <CompanyRecordID>0</CompanyRecordID> <Phone>5555551212</Phone> <EmailAddress>Donald.Campbell@blackbaud.com</EmailAddress> <PhoneTypes> <Personal>Home</Personal> <Business>Business</Business> <Email>Preferred Email</Email> </PhoneTypes> <Address> <StreetAddress>2000 Daniel Island Drive</StreetAddress> <City>Charleston</City> <State>SC</State> <ZIP>29492</ZIP> <Country>United States</Country> </Address> <RemoveSpouse>false</RemoveSpouse> <ClientUsersID>0</ClientUsersID> </Donor> <Gift> <BackOfficeID>0</BackOfficeID> <Amount>500</Amount> <IsAthon>false</IsAthon> <FinderNum>-1</FinderNum> <GiftDate>2014-11-05T19:45:33.0810547Z</GiftDate> <FundID>0</FundID> <PaymentMethod>CreditCard</PaymentMethod> <CreditType>Visa</CreditType> <CardHolderName>Donald X. Campbell</CardHolderName> <AuthorizationCode>00000</AuthorizationCode> <ReferenceNumber /> <CreditCardNumber>************1111</CreditCardNumber> <ExpirationMonth>1</ExpirationMonth> <ExpirationYear>2015</ExpirationYear> <Anonymous>false</Anonymous> <Corporate>false</Corporate> <GiftAid>false</GiftAid> <Tribute> <Name>My Nanna</Name> <Description>Life</Description> <TributeType>For the Benefit of</TributeType> <TributeID>0</TributeID> <TributeRecordID>0</TributeRecordID> <TributeID_Guid>00000000-0000-0000-0000-000000000000</TributeID_Guid> </Tribute> <IsTruePledge>false</IsTruePledge> <TruePledgeNumberOfInstallments>0</TruePledgeNumberOfInstallments> <TruePledgeInstallmentAmount>0</TruePledgeInstallmentAmount> <TruePledgeLastInstallmentAmount>0</TruePledgeLastInstallmentAmount> <IsPaymentTowardsExistingPledge>false</IsPaymentTowardsExistingPledge> <IsLastPaymentTowardsExistingPledge>false</IsLastPaymentTowardsExistingPledge> <ExistingPledgeOriginalAmt>0</ExistingPledgeOriginalAmt> <ExistingPledgeOriginalDate>0001-01-01T00:00:00</ExistingPledgeOriginalDate> <ExistingPledgeCurrentBalance>0</ExistingPledgeCurrentBalance> <ExistingPledgeDueNowAmt>0</ExistingPledgeDueNowAmt> <ExistingPledgeDueNowDate>0001-01-01T00:00:00</ExistingPledgeDueNowDate> <ExistingPledgeNextInstallAmt>0</ExistingPledgeNextInstallAmt> <ExistingPledgeNextInstallDate>0001-01-01T00:00:00</ExistingPledgeNextInstallDate> <CurrencyType>USD</CurrencyType> <ISO4217>USD</ISO4217> <MGCompany> <MGCompanyInformation> <FromMFO>false</FromMFO> <CompanyName>X Company</CompanyName> <DateLastUpdated>0001-01-01T00:00:00</DateLastUpdated> <SpecialTermsRestrictions>false</SpecialTermsRestrictions> <SpecialTermsEligibility>false</SpecialTermsEligibility> <MinimumGift>0</MinimumGift> <MaximumPerGift>0</MaximumPerGift> <DonorAnnualMaximum>0</DonorAnnualMaximum> <LifetimeTotal>0</LifetimeTotal> <MatchRatio>0</MatchRatio> <SpecialTermsRules>false</SpecialTermsRules> <System_Record_ID>0</System_Record_ID> <PluginUserCreated>false</PluginUserCreated> </MGCompanyInformation> </MGCompany> <Comments /> <TripleDES192>true</TripleDES192> <Attributes> <AttributeInformation> <AttributeTypeID>208</AttributeTypeID> <Data /> </AttributeInformation> </Attributes> <Designations> <DesignationInformation> <ID>1093</ID> <Amount>500.00</Amount> <Description>Unrestricted</Description> <BackOfficeID>53</BackOfficeID> <BackOfficeID_Guid>00000000-0000-0000-0000-000000000000</BackOfficeID_Guid> <GivingLevelID>0</GivingLevelID> </DesignationInformation> </Designations> <MarkGiftAsReceipted>false</MarkGiftAsReceipted> <eReceiptNumber>0</eReceiptNumber> <IsPaymentTowardsExistingRecurringGift>false</IsPaymentTowardsExistingRecurringGift> <CurrencySymbol>$</CurrencySymbol> </Gift> <EmailID>0</EmailID> <ProcessedDate>0001-01-01T00:00:00</ProcessedDate> <DonationContext>Other</DonationContext> <Status>Completed</Status> <FinderNum>0</FinderNum> </ShelbyDonationTran>
Nov 6 '14 #3
Dormilich
8,658 Expert Mod 8TB
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.
Nov 6 '14 #4
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?
Nov 6 '14 #5
Dormilich
8,658 Expert Mod 8TB
I downloaded the XMLEditor from TakeAwayCode.
so you do want to work with the XML manually?
Nov 6 '14 #6
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.
Nov 6 '14 #7
Luuk
1,047 Expert 1GB
Maybe you should find a (programming)tool 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 ;-)
Nov 6 '14 #8
Thanks for your input
Nov 6 '14 #9

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by PCK | last post: by
5 posts views Thread by jack | last post: by
reply views Thread by YellowAndGreen | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.