XML data and rowset view - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

XML data and rowset view

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-03-11 : 08:06:49

Mitch writes "Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
In a table, a varchar(1000) column holds a well formed XML 'document'
i.e.
<Risks><Risk pct="20">10</Risk><Risk pct="55">50</Risk><Risk pct="25">100</Risk></Risks>
or even
<Risks></Risks>
What is the fasters way to 'split' the xml column in discrete element.
The assumption is that for Risk these are the only 3 possible values
10=L low (column lRisk )
50=M medium (column mRisk )
100=H high (column hRisk )
pct means percent of the specified risk. The table has a primary key.

The result will be a new table with a row like:
lRisk lPct mRisk mPct hRisk hPct risks
L 20 M 55 H 25 <Risks><Risk pct="20">10</Risk><Risk pct="55">50</Risk><Risk pct="25">100</Risk></Risks>

The challenge is that the database has millions records so a cursor will not be acceptable.

Thanks for your time."

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-03-11 : 09:21:05

Is this what you're looking for?


-------------------------------------------------------------
--Create Old and new tables
set nocount on
declare @tb Table (risks varchar(200))
declare @newTb TAble (lrisk char(1), lPct int, mrisk char(1), mPct int, hrisk char(1), hPct int, Risks varchar(200))
insert @tb
Select '<Risks><Risk pct="20">10</Risk><Risk pct="55">50</Risk><Risk pct="25">100</Risk></Risks>' union all
Select '<Risks><Risk pct="11">10</Risk><Risk pct="24">50</Risk></Risks>' union all
Select '<Risks><Risk pct="100">10</Risk><Risk pct="2">50</Risk></Risks>' union all
Select '<Risks></Risks>' union all
Select '<Risks><Risk pct="20">10</Risk><Risk pct="30">100</Risk></Risks>'
-------------------------------------------------------------

--This assumes only 3 possible values for Risk Element (10, 50 100)
--Also assumes Risk Attributes will be in range of 0-999
insert @newTb
Select	lrisk = 'L'
	,lPct = replace(replace(subString(Risks, charindex('">10<',Risks)-3, 3),'"',''),'=','')
	,mrisk = 'M'
	,mPct = replace(replace(subString(Risks, charindex('">50<',Risks)-3, 3),'"',''),'=','')
	,hrisk = 'H'
	,hPct = replace(replace(subString(Risks, charindex('">100<',Risks)-3, 3),'"',''),'=','')
	,risks = Risks
From	@tb

SElect * from @newTb

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources