Convert xml data to table format

Question

I have a table X with one column as Body and its datatype is nvarchar(max).

The values in this column is as below( this is the body of an email which is copied and pasted in the Body column of table X through a sql server job,sample is as below). It has more than 3000 records and one record is given below.

I want to convert this data into the table form, with headers as Practice name, Partner type,Country,Region,Name,Phone Number,Email and their corresponding values.

The sample of 1 record is as below, but there are 3000 records of similar types and I want a query to convert all the 3000 records in the table form. Please suggest any sql.

The data in the body column when im trying to post as

<p></p>
Hi Team 

<p></p>

<p></p>
A web site visitor has submitted Partners on Boarding form http://abc.com.local/partners/benefits/signup.<br />
<br />


<p>Details:</p>

<p>Practice name: Test accountant</p>

<p>Partner type: Accountant</p>

<p>Country: United States</p>

<p>Region: Alabama</p>

<p>Name: Rohit Phil</p>

<p>Phone Number: 214454004</p>

<p>Email: rohit.phil@abc.com</p>

<p>Website:</p>

<p>Optional message or question:</p>

<p>Thanks,</p>

<p>The abc Website :)</p>
<br />
<br />

Similar to the format below I have another 2k records,

<table style="width: 550px;text-align:left">
    <tr>
        <td colspan="2" style="padding: 15px 0 15px 0;">
            <p>Hi Team</p>

            <p>A web site visitor has submitted Pracss on Boarding form http://abcs.com.local/Pracss/benefits/signup.</p>

            <p>
               <b>Details:</b>
            </p>
            <p>Practice name: GGriffin Corp</p>
            <p>Pracs type: Sales</p>
            <p>Country: United States</p>
            <p>Region: Ala</p>
            <p>Name: Neha George</p>
            <p>Phone Number: 123-952-4545</p>
            <p>Email: gail@nehageorge.com</p>
            <p>Website: www.nehageorge.com</p>
            <p>Optional message or question: </p>
            <p>
                Thanks,
               <br>
               <i>The abc Website :)</i>
            </p>
        </td>
    </tr>

</table>

A hint can also help me work these type of formats.

one more wired format-

Hi Danny,

My name is Anthony Paul,your Pracs Development Representative and personal resource for all things abc. I see that you had submitted a New Sales Appl recently and I wanted to reach out to you and offer some assistance. I am the first point of contact between prospective pracs and abc. My role here is to help clarify what abc is all about, inform you about our Pracs Program for Accs and Books, and help sign you up as a Pracs. But first, let me tell you a little about abc. abc is a best company that has the ability to pull and organize data from a variety of sources to increase efficiencies invoices. By gaining efficiencies with abc in your day-to-day tasks you are able to INCREASE time advising and DECREASE time entering data. It all starts with our Single Ledger on the cloud. You, your staff, and your client can be allowed access to the same data on the same system at the same time. This changes the relationship between you and your clients from one annual handover with one large fee, to an ongoing collaboration that you bill monthly. And since we are cloud-based, there is no software to download to a desktop. We give you the freedom to use your laptop, tablet or smartphone to conduct your business any where you have access to the internet. abc integrates with a variety of clients. We don't ask you to leave your current systems. Actually, we have quite a few Quickbooks Pro Advisors that are abc Pracss. We offer an exclusive FREE version of abc to Accountants and Bookkeepers called My Green abc (MGX). MGX is a great way to get your feet wet with abc because not only does it have a demo company with artificial data that you can't compromise, it also allows you to set up your own company (one free organization) so you can do your firm's books on. Along with your free version of our software, you will also receive a number of great benefits in our Pracs Program. These benefits include free education for you and your staff, free 24/7 support, and a dedicated Account Manager who will help you with things like marketing and advertisement. Your Account Manager will be you point of contact moving forward with abc. They are here to help you with any questions, as well as work alongside you throughout your abc journey. There really is no cost to join our Pracs Program for the on-demand version or $250 for the instructor-lead webinar. Once you are certified and have five clients (organizations) you qualify for Bronze status. With Bronze status you receive a 15% margin and a spot on our Trusted Advisors web page. This page is where prospective clients look for trusted advisors in their area. The higher up on the list, the greater the chances for a lead. Our Bronze Pracss are getting 2-5 referrals a month. Please respond with a good time and day for me to give you a call. At that point I can activate your FREE version of our software. During our call I will give you a brief walkthrough of My Green abc and fill you in on the great benefits of Pracsing with abc. Also, if I didn't cover what you would like or you have more questions, please don't hesitate to reach out to me directly. Find out more about abc and the Pracs Program at https://www.abcdefgh.com/us/Pracss/ Have a fantastic day, Anthony Paul Pracs Development Representative antho.paul@abcsdfc.com Mobile 720-460-1976/ 720-234-2619 7100 E. Brentwood Avenue, Suite 204, Greenwood Village, CO 80111, United States

______________________________________________________________

Hi Team

A web site visitor has submitted Pracss on Boarding form http://abc.com.local/Pracss/benefits/signup.

Details:

Practice name: sdf Solutions, LLC

Pracs type: Accs

Country: UK

Region: London

Name: Diane Hayden

Phone Number: 12345678

Email: diane@vevrbrb.com

Website: www.vevrbrbr.com

Optional message or question:

Thanks,

The abc Website :)

The above one (3rd format pasted above, the wired one) is not able to show up the xml tags. — Sachin25, Mar 12 '15 at 11:58

Rhys Jones · Answer 1 · 2015-03-12T11:50:43.073

Assuming the content will always be well formed XML you can use the XML methods to pull out the data. This should get you started;

declare @MyTable table (Body nvarchar(max))

insert @MyTable values (N'Hi Team     
A web site visitor has submitted Partners on Boarding form http://abc.com.local/partners/benefits/signup.<br />
<p>Details:</p>    
<p>Practice name: Test accountant</p>    
<p>Partner type: Accountant</p>    
<p>Country: United States</p>    
<p>Region: Alabama</p>    
<p>Name: Rohit Phil</p>    
<p>Phone Number: 214454004</p>    
<p>Email: rohit.phil@abc.com</p>    
<p>Website:</p>    
<p>Optional message or question:</p>    
<p>Thanks,</p>    
<p>The abc Website :)</p><br /><br />')

; with cte as (
    select cast(Body as XML) as XmlBody from @MyTable
)
select 
    replace(XmlBody.value('(//p/text()[contains(., "Details:")])[1]', 'varchar(100)'), 'Details:', '') as Details,
    replace(XmlBody.value('(//p/text()[contains(., "Practice name:")])[1]', 'varchar(100)'), 'Practice name:', '') as PracticeName,
    replace(XmlBody.value('(//p/text()[contains(., "Partner type:")])[1]', 'varchar(100)'), 'Partner type:', '') as PartnerType,
    replace(XmlBody.value('(//p/text()[contains(., "Country:")])[1]', 'varchar(100)'), 'Country:', '') as Country,
    replace(XmlBody.value('(//p/text()[contains(., "Region:")])[1]', 'varchar(100)'), 'Region:', '') as Region,
    replace(XmlBody.value('(//p/text()[contains(., "Name:")])[1]', 'varchar(100)'), 'Name:', '') as Name,
    replace(XmlBody.value('(//p/text()[contains(., "Phone Number:")])[1]', 'varchar(100)'), 'Phone Number:', '') as PhoneNumber,
    replace(XmlBody.value('(//p/text()[contains(., "Email:")])[1]', 'varchar(100)'), 'Email:', '') as Email,
    replace(XmlBody.value('(//p/text()[contains(., "Website:")])[1]', 'varchar(100)'), 'Website:', '') as Website,
    replace(XmlBody.value('(//p/text()[contains(., "Optional message or question:")])[1]', 'varchar(100)'), 'Optional message or question:', '') as OptionalMessageOrQuestion
from
    cte

To apply the above query to your table just replace @MyTable in the CTE definition with your real table name.

If the content isn't reliably well formed I'd be inclined to use an SP and perform string manipulation, it's not a fancy approach but it might be more robust.

Thank yu so much for the solution Rhys!! But this is not the only record .. I have 3000 different records ...how do I modify to make it work for all the records..?? — Sachin25, Mar 11 '15 at 11:19
hey Rhys , I was looking into the other record in the column, it was different from the one posted.How may I show you the sample of that record? I guess if you see these 2 types of record in the BODY column you will be in the right position to help. Thanks in advance!! — Sachin25, Mar 12 '15 at 10:29
Amazing Rhys!!! Super super stuff...it worked. Thank you so much...I havent worked on the Xquery and querying xml data so not sure how to do it. I have one more record format which has another 2k records of the same format..I was able to resolve with your solution first 3k records and now there are another 2k records with another format, can I paste them into anew request?? — Sachin25, Mar 12 '15 at 11:31
I've updated my code above. Note that your 2nd sample is not valid XML (the
after Thanks should be
). If this answer helped you then help me and click the green tick. Thanks. — Rhys Jones, Mar 12 '15 at 11:51

Convert xml data to table format

1 Answers1