CODEWITHSUNDEEP
sqlstored-proceduressql-server-2012
phoenixAZ
phoenixAZ

Reputation: 421

Syntax to read xml inside of a stored procedure

I am passing into @xCriteria a XML parameter that looks like: 

<?xml version="1.0" encoding="utf-8"?>
<searchParameters xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
     <includeInactive enabled="True" />
</searchParameters>

I know the xpath is: //includeInactive/@enabled

And have tried: 

--determine if we include inactive users
DECLARE @isInactive AS bit

-- SELECT @isInactive = @xCriteria.value('(/includeInactive/@enabled', 'bit')
-- SET @isInactive = @xCriteria.query('/includeInactive/@enabled')
-- SELECT @isInactive.value('(/includeInactive/enabled)[1]', 'bit')
-- SELECT @isInactive = @xCriteria.query('/includeInactive/@enabled')  

PRINT '@isInactive'
PRINT @isInactive

How can I fetch the boolean value?

Upvotes: 1

Views: 46

Answers (1)

marc_s
marc_s

Reputation: 755541

You try like this:

DECLARE @isInactive AS BIT;

SELECT
    @isInactive = CASE @param.value('(/searchParameters/includeInactive)[1]/@enabled', 'bit') 
                     WHEN 1 THEN 0 ELSE 1
                  END

SELECT @isInactive;

You basically need to select the first ((....)[1]) <includeInactive> node inside the root node, and then get the @enabled attribute off that node.

And you need to clunky CASE expression, since your XML stores the value of enabled, while the variable you want to get is the exact opposite - @isInactive.

Upvotes: 1

Related Questions