Conditional xml in sql

Question

I only want to show the categoryid node and value if its not an empty guid. I've tried different formats but nothing worked. It keeps just printing the if statement as a string in the results. I read that you had to have an else condition as part of your code, can I just put an empty string there? I've looked through a few articles online about this and the msdn reference with no luck. For some reason it's evaluating my if statement.

declare @XML xml = N'
<books>
    <book>
        <title>Book 1</title>
        <categoryid>00000000-0000-0000-0000-000000000000</categoryid>
        <author>Chris</author>
        <price>10</price>
    </book>   
    <book>
        <title>Book 2</title>
        <categoryid>DF3D696D-B7A3-44A2-BC7D-1D45745C979B</categoryid>
        <author>Spencer</author>
        <price>20</price>
    </book>
</books>';


select @XML.query(
'<books>
    {
        for $b in /books/book
            return 
            <book>
                {$b/title}
                {$b/price}
                if(not($b/categoryid = 00000000-0000-0000-0000-000000000000)) 
                    then {$b/categoryid}
                else 

            </book>
    }
</books>'
);

JosephStyons · Accepted Answer · 2017-02-07 14:15:15Z

I think there were two issues.

1 - The conditional block needed to be enclosed in curly braces before the "if" and after the "else" ended.

2 - The empty guid value needed to be its own variable - I think within the condition it was treating it as a string.

I also had trouble getting the "not" operator to behave as expected, so I just reversed the condition to bypass that problem.

declare @XML xml = N'
<books>
    <book>
        <title>Book 1</title>
        <categoryid>00000000-0000-0000-0000-000000000000</categoryid>
        <author>Chris</author>
        <price>10</price>
    </book>   
    <book>
        <title>Book 2</title>
        <categoryid>DF3D696D-B7A3-44A2-BC7D-1D45745C979B</categoryid>
        <author>Spencer</author>
        <price>20</price>
    </book>
</books>';

declare @emptyguid uniqueidentifier = '00000000-0000-0000-0000-000000000000'

select @XML.query('
<books>
  {
  for $b in /books/book
    return
      <book>
        {$b/title}
        {$b/price}
        {
          if($b/categoryid = sql:variable("@emptyguid")) then
            ()
          else (
            $b/categoryid
          )
        }
      </book>
  }
</books>
');

Collectives™ on Stack Overflow

Conditional xml in sql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related