Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Extracting xml data from text column Expand / Collapse
Author
Message
Posted Monday, July 19, 2010 1:29 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 15, 2013 1:38 PM
Points: 2, Visits: 61
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
Posted Tuesday, February 12, 2013 2:31 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, July 20, 2014 7:29 AM
Points: 173, Visits: 340
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
Posted Tuesday, February 12, 2013 2:45 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:49 PM
Points: 13,069, Visits: 11,908
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
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1419199
Posted Wednesday, February 13, 2013 6:36 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 15, 2013 1:38 PM
Points: 2, Visits: 61
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
Posted Wednesday, February 13, 2013 1:17 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, July 20, 2014 7:29 AM
Points: 173, Visits: 340
Ott,

Thanks for the update it's appreciated.

-Dave



Post #1419723
Posted Tuesday, February 19, 2013 8:51 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 7:13 AM
Points: 85, Visits: 867
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
Posted Tuesday, February 19, 2013 10:00 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, July 20, 2014 7:29 AM
Points: 173, Visits: 340
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 »

Add to briefcase

Permissions Expand / Collapse