January 13, 2012 at 12:05 pm
Dear All, I am facing issue while extracting data from one field. I want to split data into new table with based on XML tags. For example in attached statement I want to separate Kpi, Desc, Min, Max, Rate and Comments fields into new table. Thanks in advance.
January 13, 2012 at 12:17 pm
Are you talking about extracting the values from the XML and storing them in regular tables?
If so, XQuery is one way to get there. You'll need the node and value functions for that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 13, 2012 at 12:20 pm
Absolutely Right, but I am my Database is based on mySQL and I am new to mySQL and don't know coding. Your help will be highly appreciated!
January 13, 2012 at 12:23 pm
Here is a start :
declare @x xml
set @x =
'<?xml version="1.0"?>
<xml>
<kpis>
<kpi>
<id>1</id><desc>Maintain Servers Up and Running 99.999% Availability</desc><min>1</min><max>10</max><rate>9</rate><comment>Successful to maintain KPI</comment></kpi>
<kpi>
<id>2</id><desc>Maintain Servers Up and Running 99.999% Availability</desc><min>1</min><max>10</max><rate>9</rate><comment>Successful to maintain KPI</comment></kpi>
</kpis></xml>
'
select
id, descr
FROM @x.nodes('./xml/kpis/kpi') kpis(row)
CROSS APPLY (
SELECT kpis.row.value('id[1]', 'int')
, kpis.row.value('desc[1]', 'varchar(256)')
) As A (id, descr)
But it worksfor SQL Server, not mysql.
January 13, 2012 at 12:50 pm
waheed71 (1/13/2012)
Absolutely Right, but I am my Database is based on mySQL and I am new to mySQL and don't know coding. Your help will be highly appreciated!
You'll have to be careful with any advice you get on this site. It's an MS SQL Server site, not a MySQL site, and things we take for granted may or may not work for you.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply