locked
xquery where clause error RRS feed

  • Question

  • HI

    i am having a problem in using where clause for x.query

    i have xml column which has lot of data with different employee info

    i would like to get  the content which is releated to emp with the emp No

    i have tried with the below and gives error

    any help wold be great

    select
     xml_data.query('/posting/location') as Location
    from [Der].[dbo].[XmlImportTest]
    where xml_data.value('/posting/location/precord/num','int') = 16573

    XQuery [Der.dbo.XmlImportTest.xml_data.value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'

    Sunday, May 23, 2010 10:21 AM

Answers

  • Check this sample script:

    create table #tmp(a xml)
    insert into #tmp values('<posting>
     <cde>AU</cde>
     <nam>MC LIMITED</nam>
     <id>43 008 496 928</id>
     <id>US</id>
     <Pstr>20100322</Pstr>
     <pend>20100328</pend>
     <location>
      <pno>0156</pno>
      <pdesc>HINDLEY STREET SA - 156</pdesc>
      <precord>
       <num>148788</num>
      </precord>
     </location>
     <location>
      <pno>01561</pno>
      <pdesc>HINDLEY STREET SA - 1561</pdesc>
      <precord>
       <num>1</num>
      </precord>
     </location>
     <location>
      <pno>01562</pno>
      <pdesc>HINDLEY STREET SA - 1562</pdesc>
      <precord>
       <num>2</num>
      </precord>
     </location>
    </posting>')
    
    select 
     a.query('for $i in /posting/location
    		  where $i/precord/num="1"
    		   return $i') as Location 
    from #tmp
    
    drop table #tmp;
    Let me know if it helpful...
    Kiran(CTS)
    • Marked as answer by Bhasker Alle Sunday, May 23, 2010 12:53 PM
    Sunday, May 23, 2010 12:26 PM

All replies

  • Hi,

    Try below one:

    select
     xml_data.query('/posting/location') as Location
    from [Der].[dbo].[XmlImportTest]
    where xml_data.value('(/posting/location/precord/num)[1]','int') = 16573;

    Hope it helps.

     


    Kiran(CTS)
    Sunday, May 23, 2010 11:23 AM
  • HI Kiran

     

    it is not giving  any values

    for where clause do we have to use x.value or x.exits

    thank you

    bhasker

     

     

    Sunday, May 23, 2010 11:38 AM
  • Can you post us your sample xml?
    Kiran(CTS)
    Sunday, May 23, 2010 11:40 AM
  •  <posting>
      <cde>AU</cde>
      <nam>MC LIMITED</nam>
      <id>43 008 496 928</id>
      <id>US</id>
      <Pstr>20100322</pstr>
      <pend>20100328</pend>
    - <location>
      <pno>0156</pno>
      <pdesc>HINDLEY STREET SA - 156</pdesc>
    - <precord>
      <num>148788</num>
    </precord>
    </location>
    </posting>
     
    Sunday, May 23, 2010 11:49 AM
  • Check this sample script:

    create table #tmp(a xml)
    insert into #tmp values('<posting>
     <cde>AU</cde>
     <nam>MC LIMITED</nam>
     <id>43 008 496 928</id>
     <id>US</id>
     <Pstr>20100322</Pstr>
     <pend>20100328</pend>
     <location>
      <pno>0156</pno>
      <pdesc>HINDLEY STREET SA - 156</pdesc>
      <precord>
       <num>148788</num>
      </precord>
     </location>
     <location>
      <pno>01561</pno>
      <pdesc>HINDLEY STREET SA - 1561</pdesc>
      <precord>
       <num>1</num>
      </precord>
     </location>
     <location>
      <pno>01562</pno>
      <pdesc>HINDLEY STREET SA - 1562</pdesc>
      <precord>
       <num>2</num>
      </precord>
     </location>
    </posting>')
    
    select 
     a.query('for $i in /posting/location
    		  where $i/precord/num="1"
    		   return $i') as Location 
    from #tmp
    
    drop table #tmp;
    Let me know if it helpful...
    Kiran(CTS)
    • Marked as answer by Bhasker Alle Sunday, May 23, 2010 12:53 PM
    Sunday, May 23, 2010 12:26 PM
  • Kiran thank you very very much you have saved my life

    i am struggling for this from last 3 days

     

    • Marked as answer by Bhasker Alle Sunday, May 23, 2010 12:53 PM
    • Unmarked as answer by Kalman Toth Monday, May 24, 2010 9:01 AM
    Sunday, May 23, 2010 12:53 PM
  • Here are two more examples, using xpath and variable.

    USE tempdb;
    GO
    create table #tmp(a xml);
    
    insert into #tmp values('<posting>
     <cde>AU</cde>
     <nam>MC LIMITED</nam>
     <id>43 008 496 928</id>
     <id>US</id>
     <Pstr>20100322</Pstr>
     <pend>20100328</pend>
     <location>
     <pno>0156</pno>
     <pdesc>HINDLEY STREET SA - 156</pdesc>
     <precord>
      <num>148788</num>
     </precord>
     </location>
     <location>
     <pno>01561</pno>
     <pdesc>HINDLEY STREET SA - 1561</pdesc>
     <precord>
      <num>1</num>
     </precord>
     </location>
     <location>
     <pno>01562</pno>
     <pdesc>HINDLEY STREET SA - 1562</pdesc>
     <precord>
      <num>2</num>
     </precord>
     </location>
    </posting>');
    
    SELECT
      a.query('/posting/location[./precord/num = "1"]') AS Location
    FROM
      #tmp AS T;
    
    DECLARE @num int;
    
    SET @num = 1;
    
    SELECT
      a.query('/posting/location[./precord/num = xs:string(sql:variable("@num"))]') AS Location
    FROM
      #tmp AS T;
    
    SELECT
      a.query('/posting/location[./precord/num = xs:string(sql:variable("@num"))]') AS Location
    FROM
      #tmp AS T
    WHERE
      a.exist('/posting/location[./precord/num = xs:string(sql:variable("@num"))]') = 1;
      
    drop table #tmp;
    

     

    AMB

    Sunday, May 23, 2010 7:43 PM