Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Xquery to return rows with restricted nodes

Answered Xquery to return rows with restricted nodes

  • Thursday, February 14, 2013 9:06 AM
     
      Has Code
    Sign In to Vote
    0

    Hello All,

    I have a table where a column contains XML data. Now i want to retrieve those xml data with restriction of nodes. Kindly see the following example for more explanation on my scenario,

    declare @table table (id int, xmlfield xml)
insert into @table
select 1,'<Root xmlns="">
    <Sample>
    <Issue>
      <Level>one</Level>
      <Descp>First Example</Descp>
    </Issue>
    <Issue>
      <Level>two</Level>
      <Descp>Second Example</Descp>
    </Issue>
    </Sample>
</Root>'

    
 select * from @table

    Now i need the following result set

    Id  XML

    1   first example

    ie, for the selected level,i need the decription for it. More clearly,  the node should be restricted for <level>one</level>

    (need: What is the description for level one ?)

    thanks in advance


     

All Replies

  • Thursday, February 14, 2013 9:27 AM
     
      Has Code
    Sign In to Vote
    0

    try : 

    declare @table table (id int, xmlfield xml)
insert into @table
select 1,'<Root xmlns="">
    <Sample>
    <Issue>
      <Level>one</Level>
      <Descp>First Example</Descp>
    </Issue>
    <Issue>
      <Level>two</Level>
      <Descp>Second Example</Descp>
    </Issue>
    </Sample>
</Root>'

DECLARE @Level VARCHAR(20) = 'two'

 select s.i.value('../Descp[1]','VARCHAR(50)') 
 from @table t
 CROSS APPLY t.xmlfield.nodes('/Root/Sample/Issue/Level[text()=sql:variable("@Level")]') AS s(i)

    Thanks and regards, Rishabh K

    • Marked As Answer by DineshKumar79 Thursday, February 14, 2013 9:31 AM
    • Unmarked As Answer by DineshKumar79 Thursday, February 14, 2013 9:47 AM
    •  
     
  • Thursday, February 14, 2013 9:51 AM
     
     
    Sign In to Vote
    1

    You try Rishab's script by changing as below:

    DECLARE @Level VARCHAR(20) = 'one'

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.


     
  • Thursday, February 14, 2013 10:01 AM
     
     
    Sign In to Vote
    0

    Its working but I need to apply some more filter from the same table. Where can i apply these filter (may be ID = 2)

    Is there a possibility of having a solution without cross apply, only with ordinary where clause with xml methods ?

    • Edited by DineshKumar79 Thursday, February 14, 2013 10:03 AM
    •  
     
  • Thursday, February 14, 2013 10:13 AM
     
     
    Sign In to Vote
    1

    Is there a possibility of having a solution without cross apply, only with ordinary where clause with xml methods ?

    May be no because according to BOL the XML methods are treated as subqueries. http://msdn.microsoft.com/en-us/library/ms175894(v=sql.105).aspx

    Or if there is onlu one row in your table , then you can get that XML instance into a variable and then directly use nodes method onto it.

    Thanks and regards, Rishabh K

     
  • Thursday, February 14, 2013 10:20 AM
     
     
    Sign In to Vote
    0

    Thanks for exp, Rishabh. Right now i need to add other restrictions from the table like (where id = 4) etc. The above solution doesn't allow me to add one more filter to it. I am completely new to xqueries and Cross Apply scenarios. Please throw some ideas on this.

    Thanks

     
  • Thursday, February 14, 2013 10:22 AM
     
     
    Sign In to Vote
    1

     Right now i need to add other restrictions from the table like (where id = 4) etc.

    please post your example problem along with expected output..

    Thanks and regards, Rishabh K

     
  • Thursday, February 14, 2013 10:46 AM
     
     Proposed Has Code
    Sign In to Vote
    0

    Here's a couple of examples using the .nodes and .exist methods of the xml datatype:

    declare @table table (id int, xmlfield xml)

insert into @table
select 1,'<Root xmlns="">
    <Sample>
    <Issue>
      <Level>one</Level>
      <Descp>First Example</Descp>
    </Issue>
    <Issue>
      <Level>two</Level>
      <Descp>Second Example</Descp>
    </Issue>
    </Sample>
</Root>'

declare @level varchar(20)
set @level = 'one'


-- using .nodes and .value methods
select t.id, f.c.value('(Descp/text())[1]', 'VARCHAR(20)')
from @table t
	cross apply t.xmlField.nodes('Root/Sample/Issue[Level=sql:variable("@level")]') f(c)


-- using exist method
select id, xmlField.value('(Root/Sample/Issue/Descp/text())[1]', 'VARCHAR(20)')
from @table
where xmlField.exist('Root/Sample/Issue[Level=sql:variable("@level")]') = 1

    • Proposed As Answer by Rishabh K Thursday, February 14, 2013 10:54 AM
    •  
     
  • Thursday, February 14, 2013 10:51 AM
     
      Has Code
    Sign In to Vote
    0

    Here's the code,

    declare @table table (id int, xmlfield xml)
insert into @table
select 1,'<Root xmlns="">
    <Sample>
    <Issue>
      <Level>one</Level>
      <Descp>First Example</Descp>
    </Issue>
    <Issue>
      <Level>two</Level>
      <Descp>Second Example</Descp>
    </Issue>
    <Issue>
      <Level>one</Level>
      <Descp>Third Example</Descp>
    </Issue>
    </Sample>
</Root>'
union
select 2,'<Root xmlns="">
    <Sample>
    <Issue>
      <Level>one</Level>
      <Descp>First Example</Descp>
    </Issue>
    <Issue>
      <Level>two</Level>
      <Descp>Second Example</Descp>
    </Issue>
    <Issue>
      <Level>one</Level>
      <Descp>Third Example</Descp>
    </Issue>
    </Sample>
</Root>'

--select * from @table where id =2

DECLARE @Level VARCHAR(20) = 'two'

 select id,s.i.value('../Descp[1]','VARCHAR(50)') 
 from @table t
 CROSS APPLY t.xmlfield.nodes('/Root/Sample/Issue/Level[text()=sql:variable("@Level")]') AS s(i)

    Having the your same solution in hand and How the restrict result set for id = 2 here

     
  • Thursday, February 14, 2013 10:55 AM
     
     Answered Has Code
    Sign In to Vote
    0

    There is no reason you can't add a WHERE clause under the CROSS APPLY.  Also I would not use the parent axis (..) as performance can be poor.

    Try this:

    declare @table table (id int, xmlfield xml)

insert into @table
select 1,'<Root xmlns="">
    <Sample>
    <Issue>
      <Level>one</Level>
      <Descp>First Example</Descp>
    </Issue>
    <Issue>
      <Level>two</Level>
      <Descp>Second Example</Descp>
    </Issue>
    <Issue>
      <Level>one</Level>
      <Descp>Third Example</Descp>
    </Issue>
    </Sample>
</Root>'
union
select 2,'<Root xmlns="">
    <Sample>
    <Issue>
      <Level>one</Level>
      <Descp>First Example</Descp>
    </Issue>
    <Issue>
      <Level>two</Level>
      <Descp>Second Example</Descp>
    </Issue>
    <Issue>
      <Level>one</Level>
      <Descp>Third Example</Descp>
    </Issue>
    </Sample>
</Root>'


declare @level varchar(20)
set @level = 'one'


-- using .nodes and .value methods
select t.id, f.c.value('(Descp/text())[1]', 'VARCHAR(20)')
from @table t
	cross apply t.xmlField.nodes('Root/Sample/Issue[Level=sql:variable("@level")]') f(c)
where id = 1

-- using exist method
select id, xmlField.value('(Root/Sample/Issue/Descp/text())[1]', 'VARCHAR(20)')
from @table
where xmlField.exist('Root/Sample/Issue[Level=sql:variable("@level")]') = 1
  and id = 1

    • Marked As Answer by DineshKumar79 Thursday, February 14, 2013 11:12 AM
    •  
     
  • Thursday, February 14, 2013 10:58 AM
     
     Answered
    Sign In to Vote
    0

    Yes I forgot the exist method , thanks wbob and with where clause I thought it as SQLSwear wants to directly use nodes methods with where clause using the XML column from the table

    Thanks and regards, Rishabh K


    • Edited by Rishabh K Thursday, February 14, 2013 11:00 AM
    • Marked As Answer by DineshKumar79 Thursday, February 14, 2013 11:12 AM
    •  
     
  • Thursday, February 14, 2013 11:07 AM
     
      Has Code
    Sign In to Vote
    0

    Having the your same solution in hand and How the restrict result set for id = 2 here

    select id,s.i.value('../Descp[1]','VARCHAR(50)') 
 from @table t
 CROSS APPLY t.xmlfield.nodes('/Root/Sample/Issue/Level[text()=sql:variable("@Level")]') AS s(i)
    WHERE ID = 1

    Thanks and regards, Rishabh K

     
  • Thursday, February 14, 2013 11:14 AM
     
     
    Sign In to Vote
    0
    Thanks WBob.
     
  • Thursday, February 14, 2013 11:14 AM
     
     
    Sign In to Vote
    0
    Thanks Rishabh.