none
How to add and update element in xml data in table based on match found after search using xquery RRS feed

  • Question

  • in my table each row has one field which store xml.
    suppose my xml data look like below one which store in my table
    <?xml version="1.0" encoding="utf-8"?>
    <TickerBrokerStandardDateLineitem>
      <Ticker />
      <TickerID />
      <TickerBrokerStandardDateLineitemValues>
        <TickerBrokerStandardDateLineitemValue>
          <TabName>Consensus Model</TabName>
          <StandardDate>2010 FY</StandardDate>
          <XFundCode>TRIN</XFundCode>
          <BRTab></BRTab>
          <BRLineItem></BRLineItem>
          <StandardLineItem>Total Sales</StandardLineItem>
          <StandardValue></StandardValue>
          <ActualProvidedByCompany>NO</ActualProvidedByCompany>
        </TickerBrokerStandardDateLineitemValue>
        <TickerBrokerStandardDateLineitemValue>
          <TabName>Consensus Model</TabName>
          <StandardDate>2011 FY</StandardDate>
          <XFundCode>TRIN</XFundCode>
          <BRTab></BRTab>
          <BRLineItem></BRLineItem>
          <StandardLineItem>Total Sales</StandardLineItem>
          <StandardValue></StandardValue>
          <ActualProvidedByCompany>NO</ActualProvidedByCompany>
        </TickerBrokerStandardDateLineitemValue>
      </TickerBrokerStandardDateLineitemValues>
    </TickerBrokerStandardDateLineitem>
    now i have to update XFundCode in all records of xml data if my supplied TabName & StandardLineItem match those records

    also i have to add one element called ID=55 to each records if my supplied TabName & StandardLineItem match those records in xml data.

    so i have two task that i have to add one element called ID = 55 to each records of xml data if my TabName & StandardLineItem found in those records of xml data
    and i have to update also XFundCode element if my TabName & StandardLineItem found in those records of xml data.

    Please give me xquery code which i can run in sql server to add & update element in xml data. thanks

    Saturday, September 14, 2019 6:12 AM

All replies

  • Good day,

    You have basically two options: (1) You can parse the text and replace a value. (2) use the xml Data Type Methods like modify, insert, delete, and replace.

    Option 2 is probably preferred for most cases. You can get more information and example in the following link:
    https://docs.microsoft.com/en-us/sql/t-sql/xml/xml-data-type-methods?view=sql-server-2017

    In the following sample I added simple REPLACE and INSERT according to your basic needs:

    declare @myDoc XML = '<?xml version="1.0" encoding="utf-8"?>
    <TickerBrokerStandardDateLineitem>
      <Ticker />
      <TickerID />
      <TickerBrokerStandardDateLineitemValues>
        <TickerBrokerStandardDateLineitemValue>
          <TabName>Consensus Model</TabName>
          <StandardDate>2010 FY</StandardDate>
          <XFundCode>TRIN</XFundCode>
          <BRTab></BRTab>
          <BRLineItem></BRLineItem>
          <StandardLineItem>Total Sales</StandardLineItem>
          <StandardValue></StandardValue>
          <ActualProvidedByCompany>NO</ActualProvidedByCompany>
        </TickerBrokerStandardDateLineitemValue>
        <TickerBrokerStandardDateLineitemValue>
          <TabName>Consensus Model</TabName>
          <StandardDate>2011 FY</StandardDate>
          <XFundCode>TRIN</XFundCode>
          <BRTab></BRTab>
          <BRLineItem></BRLineItem>
          <StandardLineItem>Total Sales</StandardLineItem>
          <StandardValue></StandardValue>
          <ActualProvidedByCompany>NO</ActualProvidedByCompany>
        </TickerBrokerStandardDateLineitemValue>
      </TickerBrokerStandardDateLineitemValues>
    </TickerBrokerStandardDateLineitem>'
    
    -- before:
    select @myDoc
    
    -- modify content:
    SET @myDoc.modify('  
      replace value of (/TickerBrokerStandardDateLineitem/TickerBrokerStandardDateLineitemValues/TickerBrokerStandardDateLineitemValue/XFundCode[1]/text())[1]  
      with     "RONEN"  
    ');  
    SET @myDoc.modify('  
    	insert <ID>55</ID>          
    	as first         
    	into (/TickerBrokerStandardDateLineitem)[1]  
    '); 
    
    -- After: select @myDoc
    In your case you simply need to use UPDATE TABLE statements, and use the filter according to your needs so you will only update the rows which fit your fikter 

    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]



    Sunday, September 15, 2019 12:31 AM
    Moderator
  • probably i could not explain what i am looking for in my first post.

    there could be many records in xml. so first i need to search result by TabName='Consensus Model' AND StandardLineItem='Total Sales' if match found then only XFundCode will be updated in matched records. same way ID will be added in same match records if ID does not exist there. if ID exist then its value will be updated.

    so please share code again with modification as per my explanation. the code you shared there you are not finding data based on TabName='Consensus Model' AND StandardLineItem='Total Sales'

    how do i know ID element exist in search records or not ?

    i want to update data only after search. how to do it...please come with new sample code. thanks


    • Edited by Sudip_inn Sunday, September 15, 2019 7:40 PM
    Sunday, September 15, 2019 7:39 PM
  • Hi Sudip_inn,

    Unfortunately, the replace value of statement only updates one node at a time. 

    Check it out a solution below. SQL Server XQuery FLWOR expression power.

    SQL:

    -- DDL and sample data population, start DECLARE @tbl TABLE (ID INT IDENTITY(1,1) PRIMARY KEY, xml_data XML); INSERT INTO @tbl (xml_data) VALUES (N'<TickerBrokerStandardDateLineitem> <Ticker /> <TickerID /> <TickerBrokerStandardDateLineitemValues> <TickerBrokerStandardDateLineitemValue> <TabName>Consensus Model</TabName> <StandardDate>2010 FY</StandardDate> <XFundCode>TRIN</XFundCode> <BRTab></BRTab> <BRLineItem></BRLineItem> <StandardLineItem>Total Sales</StandardLineItem> <StandardValue></StandardValue> <ActualProvidedByCompany>NO</ActualProvidedByCompany> </TickerBrokerStandardDateLineitemValue> <TickerBrokerStandardDateLineitemValue> <TabName>Consensus Model</TabName> <StandardDate>2011 FY</StandardDate> <XFundCode>TRIN</XFundCode> <BRTab></BRTab> <BRLineItem></BRLineItem> <StandardLineItem>Total Sales</StandardLineItem> <StandardValue></StandardValue> <ActualProvidedByCompany>NO</ActualProvidedByCompany> </TickerBrokerStandardDateLineitemValue> </TickerBrokerStandardDateLineitemValues> </TickerBrokerStandardDateLineitem>'); -- DDL and sample data population, end DECLARE @XFundCode VARCHAR(20) = 'SomeValue' , @TabName VARCHAR(20) = 'Consensus Model' , @StandardLineItem VARCHAR(20) = 'Total Sales' , @ID INT = 55;

    -- before SELECT * FROM @tbl; UPDATE @tbl SET xml_data = xml_data.query('<TickerBrokerStandardDateLineitem> { for $x in /TickerBrokerStandardDateLineitem/*[not(local-name(.)=("TickerBrokerStandardDateLineitemValues"))] return $x } <TickerBrokerStandardDateLineitemValues>

    {
    for $y in /TickerBrokerStandardDateLineitem/TickerBrokerStandardDateLineitemValues/TickerBrokerStandardDateLineitemValue
    return <TickerBrokerStandardDateLineitemValue>
    {
    if (data($y/TabName)=sql:variable("@TabName") and data($y/StandardLineItem)=sql:variable("@StandardLineItem")) then
    <ID>{sql:variable("@ID")}</ID>
    else ()
    }
    {
            for $z in $y/*
            return 
            if (not(local-name($z) = ("XFundCode","ID"))) then $z
    else ( 
    if (data($y/TabName)=sql:variable("@TabName") and data($y/StandardLineItem)=sql:variable("@StandardLineItem")) then
    (
    if (local-name($z) = "XFundCode") then element XFundCode {sql:variable("@XFundCode")}
    else ()
    )
    else $z
    )
        }
    </TickerBrokerStandardDateLineitemValue>
    }

    </TickerBrokerStandardDateLineitemValues> </TickerBrokerStandardDateLineitem>'); -- after SELECT * FROM @tbl;



    Sunday, September 15, 2019 8:12 PM
  • Hi Sudip_inn,

    I slightly modified the XQuery above to accommodate newly added requirements for the ID element.

    Please don't miss it.

    Monday, September 16, 2019 4:35 AM
  • Hi Sudip_inn,

    Do you want to update XML? Or do you have another table?Could you explain  your logic more clearly and share us your expected result?

     

    In your original xml , Both of your two rows satisfy this condition (TabName='Consensus Model' AND StandardLineItem='Total Sales') . 

     

    I have change some useful information in your xml  into a table. Please provide more information.

     
    DECLARE @string varchar(max)
    DECLARE @xml xml 
    SET @string='
    <?xml version="1.0" encoding="utf-8"?>
    <TickerBrokerStandardDateLineitem>
      <Ticker />
      <TickerID />
      <TickerBrokerStandardDateLineitemValues>
        <TickerBrokerStandardDateLineitemValue>
          <TabName>Consensus Model</TabName>
          <StandardDate>2010 FY</StandardDate>
          <XFundCode>TRIN</XFundCode>
          <BRTab></BRTab>
          <BRLineItem></BRLineItem>
          <StandardLineItem>Total Sales</StandardLineItem>
          <StandardValue></StandardValue>
          <ActualProvidedByCompany>NO</ActualProvidedByCompany>
        </TickerBrokerStandardDateLineitemValue>
        <TickerBrokerStandardDateLineitemValue>
          <TabName>Consensus Model</TabName>
          <StandardDate>2011 FY</StandardDate>
          <XFundCode>TRIN</XFundCode>
          <BRTab></BRTab>
          <BRLineItem></BRLineItem>
          <StandardLineItem>Total Sales</StandardLineItem>
          <StandardValue></StandardValue>
          <ActualProvidedByCompany>NO</ActualProvidedByCompany>
        </TickerBrokerStandardDateLineitemValue>
      </TickerBrokerStandardDateLineitemValues>
    </TickerBrokerStandardDateLineitem> ' 
    
    set @xml= stuff(cast(@string as varchar(max)),1,charindex('>',cast(@string as varchar(max))),'')
    
    select  T.C.value('TabName[1]','varchar(max)') as TabName,
            T.C.value('StandardDate[1]','varchar(max)') as StandardDate,
            T.C.value('StandardLineItem[1]','varchar(max)') as StandardLineItem,
            T.C.value('XFundCode[1]','varchar(max)') as XFundCode
    from    @xml.nodes('/TickerBrokerStandardDateLineitem/TickerBrokerStandardDateLineitemValues/TickerBrokerStandardDateLineitemValue') as T ( C )
    /*
    TabName            StandardDate       StandardLineItem    XFundCode
    ------------------ ------------------ ------------------- -----------
    Consensus Model    2010 FY            Total Sales         TRIN
    Consensus Model    2011 FY            Total Sales         TRIN
    */
    

    Hope it will help you.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, September 16, 2019 6:49 AM
  • you just select data from xml but i want to update data in xml based on search. after search only update will be perform in match data....not all data. i want to search above xml by section & lineitem name if match found then i need to update xfund code there...how can i do it?

    also i need to search above xml by section & lineitem name if match found then i need to update ID there in xml. if ID not found then i need to add ID element in search data.

    please share sample code how to achieve this. thanks

    Monday, September 16, 2019 8:43 AM
  • Hi,

    As your said that 'in search data', I am sorry that I could not understand clearly. Do you have another table?

    Could you please  share us your expected result?

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, September 16, 2019 9:23 AM
  • Hi Sudip_inn,

    You are still struggling to explain what is needed, when the solution is already at hand.

    Why wouldn't you try it?

    Monday, September 16, 2019 2:05 PM
  • Hi ,

     

    I am writing to follow up this thread with you. Have you solved your issue? If you have solved your issue ,in order to close this thread, please kindly mark helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, September 23, 2019 9:50 AM