TSQL stored procedure and XQuery?

Question

Hello,

I want to write a stored procedure that queries XML files after I have input a certain string pattern to look for.

I'm already stuck at the input parameters, consider the following XML-document.

<root>
  <container>
      <element>A</element>
      <option>1</option>
  </container>
  <container>
      <element>B</element>
      <option>-1</option>
  </container>
  <container>
      <element>C</element>
  </container>
</root>

What I want to achieve is find and output a certain pattern combining element-tag and option-tag to a table.

For example: EXEC search "A1,B-1,C" is the input string and would be true which then needs to be put in a table. But "A,B,C" would be false.

I'm not that great with TSQL, so I don't know how I could split or arrange the search pattern so that I could use this to work with both element-tag and option-tag and put them into variables or so.

EDIT: The code below I think is going in the right direction but I have another big issue. I need to analyze each value of the pattern with a corresponding table.

I better give an example: Input is "A1,B-1,C" btw input length should be flexible.

In my existing table I have 4 columns with the following data:

  ID| Value | Meaning | Info
  1 |   A   |   text  | text
  2 |   A-1 |   text  | text
  3 |   A1  |   text  | text
  4 |   B   |   text  | text
  5 |   B-1 |   text  | text

and so on...

Now somehow I need to check each single input-string with the value and output the input-string with both "Meaning" and "Info" column to another table.

With the example above I would have to find the sequence of "A1,B-1,C" and then output the corresponding text (including the string) of the table above to a new table. So that it could look like this:

  | Value | Meaning | Info
  |   A1  |   text  | text
  |   B-1 |   text  | text
  |   C   |   text  | text

I don't know if I'm making it too complicated with the above table or if a CASE / IF-ELSE structure in the procedure would work better.

Does anyone know how this can be achieved?

Answer 1

It looks as if you're using the wrong tools for the job but if these are pretty firm constraints you're working against then you could use the following, for example:

drop table #xml
drop table #queryparams

declare @x xml
select @x = '<code><root><items><element>A</element><option>1</option></items><items><element>B</element><option>-1</option></items><items><element>C</element></items></root></code>'

create table #xml (element varchar(60), [option] int null)

insert into #xml
select code.item.value('(element)[1]', 'varchar(60)'),
  code.item.value('(option)[1]', 'int')
from 
  @x.nodes('/code/root/items') AS code(item)

declare @queryParam varchar(60)
select @queryParam = 'A1,B-1,C'

create table #queryparams (element varchar(60), [option] int null)

insert into #queryparams
select left(data,1), RIGHT(data, len(data)-1) 
from dbo.split(@queryParam,',')

if not exists (
 select *
 from #xml x
 left join #queryparams q
  on x.element = q.element
 where q.[option] is null
)
select 1
else
select 0

As marc_s suggests you're better off with <element> and <option> tags inside a common tag. I've opted for the poorly named <items> in this case.

This solution is nasty enough to suggest the approach isn't quite right somehow.