Extracting xml data from text column

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 General Discussion » Extracting xml data from text column

Extracting xml data from text column

Rate Topic

Display Mode

Topic Options

Author

Message

ott2497

Posted Monday, July 19, 2010 1:29 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, February 13, 2013 9:46 AM
Points: 2, Visits: 60

I'm very new sql server (oracle dba). I was assigned to work with sql server 2005. The user wants a report where data is in xml format. The table creation's script is below. The xml column, datatype text, has xml format data. I need to extract it and create report. If I could extract into a table, then I'm all set. But I don't know how to extract it.
Any help will be appreciated.

Thanks

CREATE TABLE [dbo].[job](
[id] [int] NOT NULL,
[submitTime] [datetime] NULL,
[stopTime] [datetime] NULL,
[priority] [int] NULL,
[xml] [text] NULL, <=== xml data in this column
[display] [bit] NULL,
[title] [varchar](256) NULL,
[author] [varchar](50) NULL,
[taskstarts] [int] NULL,
[taskstops] [int] NULL,
[ntasks] [int] NULL,
[failed] [bit] NULL,
[userdata] [text] NULL,
[reservedtime] [int] NULL,
[nodename] [varchar](256) NULL,
[actualSubmitTime] [datetime] NULL,
[idMain] [int] NULL,
CONSTRAINT [PK_job_jobid] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY

Post #955067

davidsalazar01

Posted Tuesday, February 12, 2013 2:31 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 3:49 PM
Points: 170, Visits: 332

Hi,

Did you ever get an answer/solution on this topic? I have the same issue and don't know the answer.

Please let me know.

Thanks!

-Dave

Post #1419192

Sean Lange

Posted Tuesday, February 12, 2013 2:45 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 1:17 PM
Points: 8,641, Visits: 8,273

davidsalazar01 (2/12/2013)

Hi,

Did you ever get an answer/solution on this topic? I have the same issue and don't know the answer.

Please let me know.

Thanks!

-Dave

This thread is 2 1/2 years old. You will likely have better luck starting a new one.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Post #1419199

ott2497

Posted Wednesday, February 13, 2013 6:36 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, February 13, 2013 9:46 AM
Points: 2, Visits: 60

Since I got no response and I found that I could do it in Oracle, so I extracted data daily from sqlserver database and loaded into Oracle database. From then, I wrote pl/sql using extractvalue to get data that I wanted. It's more complicated but I had reports for users.

I found that dealing with xml in Oracle is easier than sqlserver.

Good Luck!

Post #1419474

davidsalazar01

Posted Wednesday, February 13, 2013 1:17 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 3:49 PM
Points: 170, Visits: 332

Ott,

Thanks for the update it's appreciated.

-Dave

Post #1419723

Davio

Posted Tuesday, February 19, 2013 8:51 AM

SSC Journeyman

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 9:20 AM
Points: 76, Visits: 436

I was asked to do the very same thing with XML a few weeks ago and this is a slightly simplified version of what I came up with:

declare @stringXML varchar(max), @XMLData as xml
set @stringXML =
'<app-data>
<FlowsNotification trandate="09/19/2008" type="Contribution" manager="BCA" custodian="">
<Contribution>
<transaction id="cm2428382">
<snam>TF1012</snam>
<style>DFISPM</style>
<date>09/18/2008</date>
<include>true</include>
<amount>200000.00</amount>
<originalamount>200000.00</originalamount>
<description>$JNL :Funds trans from acct (97302287)</description>
<accountid>97527748</accountid>
<custid>164</custid>
<custodian>CS</custodian>
<isAccountInCDB>true</isAccountInCDB>
</transaction>
</Contribution>
</FlowsNotification>
<account-data>
<account custAcctNo="97527748" custID="164" />
</account-data>
</app-data>'

set @XMLData = CAST(@stringXML AS XML)
select @XMLData as XMLData into #XMLTable

select x.y.value('Contribution[1]/transaction[1]/@id[1]','VARCHAR(20)') AS tranid,
x.y.value('@trandate[1]','Date') AS NotificationTranDate,
x.y.value('@manager[1]', 'CHAR(10)') AS MgrCode,
x.y.value('Contribution[1]/transaction[1]/snam[1]','VARCHAR(50)') AS ACCTsnam,
x.y.value('Contribution[1]/transaction[1]/style[1]','VARCHAR(20)') AS style,
x.y.value('Contribution[1]/transaction[1]/date[1]','Date') AS TranDate,
Case when x.y.value('Contribution[1]/transaction[1]/include[1]','varchar(50)') = 'true' then 1 else 0 end AS include,
x.y.value('Contribution[1]/transaction[1]/amount[1]','Float') AS amount,
x.y.value('Contribution[1]/transaction[1]/originalamount[1]','Float') AS originalamount,
x.y.value('Contribution[1]/transaction[1]/description[1]','VARCHAR(255)') AS tranDescription,
x.y.value('Contribution[1]/transaction[1]/accountid[1]','VARCHAR(50)') AS accountid,
x.y.value('Contribution[1]/transaction[1]/custid[1]','integer') AS custid,
x.y.value('Contribution[1]/transaction[1]/custodian[1]','varchar(20)') AS custodian,
Case when x.y.value('Contribution[1]/transaction[1]/isAccountInCDB[1]','varchar(50)') = 'true' then 1 else 0 end AS isAccountInCDB
FROM #XMLTable T
CROSS APPLY T.XMLData.nodes('app-data/FlowsNotification') x(y)

drop table #XMLTable

The query pulls out both elements and values of the sample XML provided.

I found the XML examples by Jacob Sebastian site very helpful http://beyondrelational.com/modules/2/blogs/28/posts/10279/xquery-labs-a-collection-of-xquery-sample-scripts.aspx.

Post #1421692

davidsalazar01

Posted Tuesday, February 19, 2013 10:00 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 3:49 PM
Points: 170, Visits: 332

I ended up using ths logic below and it did the trick.

convert(xml,pm.XmlProfile_TXT).value('/XMLProfile[1]/DID[1]', 'varchar(30)')

Thanks everybody for your input and good suggestions! This post is now completed.

-Dave

Post #1421740

« Prev Topic | Next Topic »

Permissions