July 19, 2010 at 1:29 pm
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
February 12, 2013 at 2:31 pm
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
February 12, 2013 at 2:45 pm
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 13, 2013 at 6:36 am
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!
February 13, 2013 at 1:17 pm
Ott,
Thanks for the update it's appreciated.
-Dave
February 19, 2013 at 8:51 am
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.
February 19, 2013 at 10:00 am
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply