none
How to search data in xml by xquery RRS feed

  • Question

  • suppose this is my xml. now how could i search in below xml by xquery based on TabName & StandardLineItem ? looking for sample code.

    <?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>

    Sunday, September 15, 2019 8:23 PM

Answers

  • Hi Sudip_inn,

     

    Would you  like this one?

     
    IF OBJECT_ID('MyTable') IS NOT NULL drop table  MyTable 
    go
    create table MyTable (Roles XML)
    
    insert into MyTable values
    ('<?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> Sales</StandardLineItem>
          <StandardValue></StandardValue>
          <ActualProvidedByCompany>NO</ActualProvidedByCompany>
        </TickerBrokerStandardDateLineitemValue>
      </TickerBrokerStandardDateLineitemValues>
    </TickerBrokerStandardDateLineitem>')
    
    
    select
      Roles.value('(/TickerBrokerStandardDateLineitem/TickerBrokerStandardDateLineitemValues/TickerBrokerStandardDateLineitemValue/TabName)[1]', 'varchar(50)' ) [TabName],
      Roles.value('(/TickerBrokerStandardDateLineitem/TickerBrokerStandardDateLineitemValues/TickerBrokerStandardDateLineitemValue/StandardLineItem)[1]', 'varchar(50)' ) [StandardLineItem]
    from
      MyTable
    
      /*TabName                                            StandardLineItem
    -------------------------------------------------- --------------------------------------------------
    Consensus Model                                    Total Sales
      */
    

    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.

    • Marked as answer by Sudip_inn Tuesday, September 17, 2019 6:56 AM
    Monday, September 16, 2019 3:32 AM
  • Or this one?

    IF OBJECT_ID('MyTable') IS NOT NULL drop table  MyTable 
    go
    create table MyTable (Roles XML)
    
    insert into MyTable values
    ('<?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> Sales</StandardLineItem>
          <StandardValue></StandardValue>
          <ActualProvidedByCompany>NO</ActualProvidedByCompany>
        </TickerBrokerStandardDateLineitemValue>
      </TickerBrokerStandardDateLineitemValues>
    </TickerBrokerStandardDateLineitem>')
    
    
    
    select  A.C.value('TabName[1]','varchar(max)') as TabName,
            A.C.value('StandardDate[1]','varchar(max)') as StandardDate,
            A.C.value('StandardLineItem[1]','varchar(max)') as StandardLineItem,
            A.C.value('XFundCode[1]','varchar(max)') as XFundCode
    from    Mytable T
    cross apply Roles.nodes('/TickerBrokerStandardDateLineitem/TickerBrokerStandardDateLineitemValues/TickerBrokerStandardDateLineitemValue') as A (C)
    /*
    TabName            StandardDate       StandardLineItem    XFundCode
    ------------------ ------------------ ------------------- -----------
    Consensus Model    2010 FY            Total Sales         TRIN
    Consensus Model    2011 FY            Total Sales         TRIN
    */
    

    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.

    • Marked as answer by Sudip_inn Tuesday, September 17, 2019 6:56 AM
    Monday, September 16, 2019 6:42 AM

All replies

  • Hi Sudip_inn,

     

    Would you  like this one?

     
    IF OBJECT_ID('MyTable') IS NOT NULL drop table  MyTable 
    go
    create table MyTable (Roles XML)
    
    insert into MyTable values
    ('<?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> Sales</StandardLineItem>
          <StandardValue></StandardValue>
          <ActualProvidedByCompany>NO</ActualProvidedByCompany>
        </TickerBrokerStandardDateLineitemValue>
      </TickerBrokerStandardDateLineitemValues>
    </TickerBrokerStandardDateLineitem>')
    
    
    select
      Roles.value('(/TickerBrokerStandardDateLineitem/TickerBrokerStandardDateLineitemValues/TickerBrokerStandardDateLineitemValue/TabName)[1]', 'varchar(50)' ) [TabName],
      Roles.value('(/TickerBrokerStandardDateLineitem/TickerBrokerStandardDateLineitemValues/TickerBrokerStandardDateLineitemValue/StandardLineItem)[1]', 'varchar(50)' ) [StandardLineItem]
    from
      MyTable
    
      /*TabName                                            StandardLineItem
    -------------------------------------------------- --------------------------------------------------
    Consensus Model                                    Total Sales
      */
    

    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.

    • Marked as answer by Sudip_inn Tuesday, September 17, 2019 6:56 AM
    Monday, September 16, 2019 3:32 AM
  • Or this one?

    IF OBJECT_ID('MyTable') IS NOT NULL drop table  MyTable 
    go
    create table MyTable (Roles XML)
    
    insert into MyTable values
    ('<?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> Sales</StandardLineItem>
          <StandardValue></StandardValue>
          <ActualProvidedByCompany>NO</ActualProvidedByCompany>
        </TickerBrokerStandardDateLineitemValue>
      </TickerBrokerStandardDateLineitemValues>
    </TickerBrokerStandardDateLineitem>')
    
    
    
    select  A.C.value('TabName[1]','varchar(max)') as TabName,
            A.C.value('StandardDate[1]','varchar(max)') as StandardDate,
            A.C.value('StandardLineItem[1]','varchar(max)') as StandardLineItem,
            A.C.value('XFundCode[1]','varchar(max)') as XFundCode
    from    Mytable T
    cross apply Roles.nodes('/TickerBrokerStandardDateLineitem/TickerBrokerStandardDateLineitemValues/TickerBrokerStandardDateLineitemValue') as A (C)
    /*
    TabName            StandardDate       StandardLineItem    XFundCode
    ------------------ ------------------ ------------------- -----------
    Consensus Model    2010 FY            Total Sales         TRIN
    Consensus Model    2011 FY            Total Sales         TRIN
    */
    

    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.

    • Marked as answer by Sudip_inn Tuesday, September 17, 2019 6:56 AM
    Monday, September 16, 2019 6:42 AM