473,396 Members | 1,671 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,396 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 3515
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

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

Similar topics

1
by: Hans | last post by:
I have a Windows.Forms.Form containing a DataGrid where the DataSource is a DataView. Everything refreshes fine after adding, deleting or editing rows. When I close the dialog and reopen it with a...
1
by: PCK | last post by:
I am trying to select a single cell from an Excel workbook. I am using the following code. strCN = GetExcelConnection("C:\Test\Excel.xls") strCMD = "select * from " oleDbCN = New...
9
by: sellcraig | last post by:
Microsoft access 2 tables table "data main" contains a field called "code" table "ddw1" is created from a make table query of "data main" Goal- the data in "code" field in needs to...
13
by: kdee | last post by:
Hi to everyone! I'm actually a beginner in access/vba etc. Like many of you, I also have a problem with something. That »something« is called »How to get values from a field in a table or a...
9
by: RMC | last post by:
Hello, I'm looking for a way to parse/format a memo field within a report. The Access 2000 database (application) has an equipment table that holds a memo field. Within the report, the memo...
5
by: jack | last post by:
Now here is the requirement to edit single Cell in the datagrid. Im an trying to create a arithematic datagrid which will calculate the area and the cost relating to it. The functionality when...
1
by: cristus | last post by:
hi, it's this posible in excel/vba? write all data from web import into single cell (concatenate all data and write to a single cell) Thank you
6
by: taxmanandy | last post by:
my linked database has an email fields that shows data as joe@gmail.com#mailto:joe@gmail.com#. I want to strip the #...# so new field is joe@gmail.com. i am a access novice and need help thanks Andy
2
by: PreethiGowri | last post by:
Can we store multiple values or range of values in a single cell, something like 1111-1115 i.e a single cell in the table should store the values 1111,1112,1113,1114,1115? If yes, then how do we do...
0
by: Syed Hadi | last post by:
i want a single cell click function... but when i am pressing anywhere in the datgridview this cell is working ... how to use single cell..in dgv private void dataGridView1_CellClick(object...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
0
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
0
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...

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.