Query an xml column in SQL

I have a table called Orders, which holds an xml column called orderxml.
I need to query the table

The table orders has the following  columns'
OrderID
OrderRegion
OrderXml
CreatedDate

here is the xml column data
<OrderRecord>
  <codes datatype="order">  
    <testcode code="100" description="SODIUM" frequency=""  />
    <testcode code="104" description="GLUCOSE" frequency=""  />
    <testcode code="106D" description="CREATININE" frequency=""  />
    <testcode code="107" description="CALCIUM" frequency=""  />
    <testcode code="108" description="ALT" frequency=""  />
  </codes>
</OrderRecord>

I need to query the table where the code = "100" in the OrderXml column and the OrderRegion = "East"
LVL 1
countrymeisterAsked:
Who is Participating?
 
cyberkiwiConnect With a Mentor Commented:
Use an sql:variable


declare @testcode varchar(10)
set @testcode = '100'
select *
from orders
where OrderRegion = 'East'
  and OrderXml.exist('/OrderRecord/codes/testcode[@code=sql:variable("@testcode")]') = 1
0
 
cyberkiwiCommented:
select *
from orders
where OrderRegion = 'East'
  and OrderXml.exist('/OrderRecord/codes/testcode[@code=100]') = 1

Open in new window

Sample DDL and data
create table orders(
OrderID int,
OrderRegion varchar(100),
OrderXml xml,
CreatedDate datetime)
insert orders select 1, 'East', '
<OrderRecord>
  <codes datatype="order">  
    <testcode code="100" description="SODIUM" frequency=""  />
    <testcode code="104" description="GLUCOSE" frequency=""  />
    <testcode code="106D" description="CREATININE" frequency=""  />
    <testcode code="107" description="CALCIUM" frequency=""  />
    <testcode code="108" description="ALT" frequency=""  />
  </codes>
</OrderRecord>', GETDATE()

Open in new window

0
 
countrymeisterAuthor Commented:
cyberkiwi:

The above example works fine, but in my case I need to create a stored procedure to get the value, so I need to pass a parameter value
the following does not work

--drop table orders
create table orders(
OrderID int,
OrderRegion varchar(100),
OrderXml xml,
CreatedDate datetime)
insert orders select 1, 'East', '
<OrderRecord>
  <codes datatype="order">  
    <testcode code="100" description="SODIUM" frequency=""  />
    <testcode code="104" description="GLUCOSE" frequency=""  />
    <testcode code="106D" description="CREATININE" frequency=""  />
    <testcode code="107" description="CALCIUM" frequency=""  />
    <testcode code="108" description="ALT" frequency=""  />
  </codes>
</OrderRecord>', GETDATE()

declare @testcode varchar(10)
set @testcode = '100'
select *
from orders
where OrderRegion = 'East'
  and OrderXml.exist('/OrderRecord/codes/testcode[@code=@testcode]') = 1
0
 
countrymeisterAuthor Commented:
Thanks
0
All Courses

From novice to tech pro — start learning today.