help me for Find and replace

Question

Post reply

help me for Find and replace

thava

SSCrazy

Points: 2275
More actions
September 3, 2013 at 7:49 am

#298380

i have the following strucrue and the data as folloows
DECLARE @DetTbl AS TABLE (id INT IDENTITY (1, 1), Detail NVARCHAR (500))
DECLARE @data AS TABLE (id INT IDENTITY (1, 1), DetVal NVARCHAR (500), Detid INT)
INSERT INTO @DetTbl
VALUES
('Data1 is a Data2'), ('Data3 is Not a Data1')
INSERT INTO @data
VALUES
(
'<keys>
<Key>
<KeyID>Data1</KeyID>
<KeyVal>Cow</KeyVal>
</Key>
<Key>
<KeyID>Data2</KeyID>
<KeyVal>Animal</KeyVal>
</Key>
</keys>', 1
),
(
'<keys>
<Key>
<KeyID>Data1</KeyID>
<KeyVal>Parrot</KeyVal>
</Key>
<Key>
<KeyID>Data2</KeyID>
<KeyVal>Bird</KeyVal>
</Key>
</keys>', 1
),
(
'<keys>
<Key>
<KeyID>Data3</KeyID>
<KeyVal>Table</KeyVal>
</Key>
<Key>
<KeyID>Data1</KeyID>
<KeyVal>Animal</KeyVal>
</Key>
</keys>', 2
)
I want the result as follows
Cow is a Animal
Parrot is a Bird
Table is Not a Animal
so for i have down this
;WITH cte AS
(
SELECT d.id, CAST (d.DetVal AS XML) AS DetVal, d.Detid, dt.Detail
FROM @data d
INNER JOIN @DetTbl dt
ON dt.id = d.Detid
),
x AS(
SELECT detail, kid, kvalue
FROM cte d
CROSS APPLY(
SELECT a.b.value ('KeyID[1]', 'varchar(100)') AS KID, a.b.value ('KeyVal[1]', 'varchar(100)') AS
KValue
FROM DetVal.nodes ('keys/Key') AS a (b)
)x)
SELECT * FROM x
any idea
Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script [/url]

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

thava SSCrazy Points: 2275 More actions · Answer 1

is this a tough one

Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script [/url]

HanShi SSC-Dedicated Points: 33506 More actions · Answer 2

This will give you the desired results:

;WITH CTE_XML (Detail, DetVal, Data1, Data2, Data3) AS (

SELECT

DetTbl.Detail

, Data.DetVal

,DetVal.value('(/keys/Key/KeyID[. = "Data1"]/../KeyVal)[1]', 'varchar(50)') AS [Data1]

,DetVal.value('(/keys/Key/KeyID[. = "Data2"]/../KeyVal)[1]', 'varchar(50)') AS [Data2]

,DetVal.value('(/keys/Key/KeyID[. = "Data3"]/../KeyVal)[1]', 'varchar(50)') AS [Data3]

FROM @data Data

inner join @DetTbl DetTbl

on Data.Detid = DetTbl.id

)

select

replace(

replace(Detail, 'Data1', ISNULL(Data1, ''))

, 'Data2', ISNULL(Data2, ''))

, 'Data3', ISNULL(Data3, ''))

as new_Detail

from CTE_XML

The article on https://www.simple-talk.com/sql/database-administration/ad-hoc-xml-file-querying/ is a good one to learn about querying XML data.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

thava SSCrazy Points: 2275 More actions · Answer 3

thanks for your ref but is it not hard coded, is there a way we can do it dynamically,

Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script [/url]

HanShi SSC-Dedicated Points: 33506 More actions · Answer 4

thava (9/4/2013)
...but is it not hard coded, is there a way we can do it dynamically,

What part is not hard coded? Are you talking about the definition/lay-out of the XML? Do you mean the values in @DetVal are not hard-coded?

Can you give multiple samples of the dynamic parts?

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

Mark Cowne One Orange Chip Points: 26944 More actions · Answer 5

Try this

WITH Source AS (

SELECT d.id,

CAST (d.DetVal AS XML) AS DetVal,

d.Detid,

dt.Detail

FROM @data d

INNER JOIN @DetTbl dt ON dt.id = d.Detid),

Data AS (

SELECT Detid,

id,

Detail,

x.r.value('(KeyID)[1]','NVARCHAR (500)') AS [Key],

x.r.value('(KeyVal)[1]','NVARCHAR (500)') AS [Value],

ROW_NUMBER() OVER(PARTITION BY Detid,id ORDER BY x.r) AS rn

FROM Source

CROSS APPLY DetVal.nodes('/keys/Key') AS x(r)),

Recur AS (

SELECT Detid,

id,

rn,

REPLACE(Detail,[Key],Value) AS Detail

FROM Data

WHERE rn=1

UNION ALL

SELECT d.Detid,

d.id,

d.rn,

REPLACE(r.Detail,d.[Key],d.Value) AS Detail

FROM Data d

INNER JOIN Recur r ON r.Detid = d.Detid

AND r.id = d.id

AND r.rn+1 = d.rn)

SELECT r.Detail

FROM Recur r

WHERE NOT EXISTS(SELECT * FROM Data d

WHERE d.Detid = r.Detid

AND d.id = r.id

AND d.rn = r.rn + 1);

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

thava SSCrazy Points: 2275 More actions · Answer 6

that was awesome this is what i Want,

just for curiosity

the recursive Cte take the fist record and process it further with the rest of the Data's found in xml, is it right ?

and then why is the not exists that is little confuse

Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script [/url]

Mark Cowne One Orange Chip Points: 26944 More actions · Answer 7

the recursive Cte take the fist record and process it further with the rest of the Data's found in xml, is it right ?

Yep, correct.

and then why is the not exists that is little confuse

The recursive CTE effectively iterates over the Key,Value pairs and applies them in turn to the Detail so you get the effect of nested REPLACEs. The NOT EXISTS clause at the end is to ensure you only get the results of the final iteration. As an exercise, try removing the NOT EXISTS clause, you'll see all of the partial results.

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537