September 3, 2013 at 7:49 am
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]
September 4, 2013 at 3:08 am
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]
September 4, 2013 at 4:02 am
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(
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.
September 4, 2013 at 5:43 am
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]
September 4, 2013 at 6:02 am
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?
September 4, 2013 at 6:34 am
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/61537September 4, 2013 at 7:54 am
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]
September 4, 2013 at 8:00 am
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/61537Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply