help me for Find and replace

  • 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]

  • 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]

  • 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.

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
  • 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]

  • 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’! **
  • 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
  • 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]

  • 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

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

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