xml dml conditional attribute inserting does not work   

Hi,

I'm experimenting with XML DML. I need to add an attribute only if it meet 
some condition. But I can not use an sql variable to implement the 
condition. The following example gives me an error:
XQuery [modify()]: An expression was expected

Declare @sSerie Varchar(10)
  ,@sFolio Varchar(10)

Declare @xmlCfd xml
Set @xmlCfd = ''
Set @xmlCfd.modify('
  declare namespace cfd="http://www.sat.gob.mx/cfd/2";
  insert (
    <Comprobante>
      {
        if ({sql:variable("@sSerie")} = 2)
        then attribute serie {sql:variable("@sSerie")}
        else ()
        ,attribute folio {sql:variable("@sFolio")}
      }
    </Comprobante>
  )
  into (/)[1]'
)

Select @xmlCfd

Obviously, the condition I need should look like
if ({sql:variable("@sSerie")} != '')

How to implement such an expression?

Any hint is welcomed
Thanks in advance
Sammy

date: Tue, 1 Jul 2008 14:17:43 -0500   author:   SammyBar

RE: xml dml conditional attribute inserting does not work   

Got these two to work, but using element:

DECLARE @sSerie VARCHAR(10),
		@sFolio VARCHAR(10)

DECLARE @xmlCfd XML

SET @xmlCfd = ''
SET @sSerie = 2 -- Or 3
SET @sFolio = 3

-- Go easy on yourself and check the condition in SQL
IF @sSerie = 2
	SET @xmlCfd.modify('declare namespace cfd="http://www.sat.gob.mx/cfd/2";
	  insert (<Comprobante>{sql:variable("@sSerie")}</Comprobante>)
	  into (/)[1]')
ELSE
	SET @xmlCfd.modify('declare namespace cfd="http://www.sat.gob.mx/cfd/2";
	  insert (<Comprobante>{sql:variable("@sFolio")}</Comprobante>)
	  into (/)[1]')

SELECT @xmlCfd

-- Reset
-- XQuery if method 
SET @xmlCfd = ''
SET @xmlCfd.modify('declare namespace cfd="http://www.sat.gob.mx/cfd/2";
insert 
if (sql:variable("@sSerie")="2")
then element Comprobante {sql:variable("@sSerie")}
else element Comprobante {sql:variable("@sFolio")}
into (/)[1]')

SELECT @xmlCfd

This returns:
<Comprobante>2</Comprobante>

I know you mentioned attributes, but you can't have valid xml with 
attributes but no elements right?

Post your expected results if this doesn't help.

HTH
wBob

"SammyBar" wrote:

> Hi,
> 
> I'm experimenting with XML DML. I need to add an attribute only if it meet 
> some condition. But I can not use an sql variable to implement the 
> condition. The following example gives me an error:
> XQuery [modify()]: An expression was expected
> 
> Declare @sSerie Varchar(10)
>   ,@sFolio Varchar(10)
> 
> Declare @xmlCfd xml
> Set @xmlCfd = ''
> Set @xmlCfd.modify('
>   declare namespace cfd="http://www.sat.gob.mx/cfd/2";
>   insert (
>     <Comprobante>
>       {
>         if ({sql:variable("@sSerie")} = 2)
>         then attribute serie {sql:variable("@sSerie")}
>         else ()
>         ,attribute folio {sql:variable("@sFolio")}
>       }
>     </Comprobante>
>   )
>   into (/)[1]'
> )
> 
> Select @xmlCfd
> 
> Obviously, the condition I need should look like
> if ({sql:variable("@sSerie")} != '')
> 
> How to implement such an expression?
> 
> Any hint is welcomed
> Thanks in advance
> Sammy 
> 
> 
>

date: Tue, 1 Jul 2008 13:46:45 -0700   author:   Bob

Re: xml dml conditional attribute inserting does not work   

Yes!
That hint is just what I was looking for...

Declare
     @sSerie Varchar(10)
    ,@sFolio Varchar(10)

--Select @sSerie = '123'
Select @sFolio = '456'

Declare @xmlCfd xml
Set @xmlCfd = ''
Set @xmlCfd.modify('
    declare namespace cfd="http://www.sat.gob.mx/cfd/2";
    insert (
        <Comprobante>
        {
            if (sql:variable("@sSerie") != "")
            then attribute serie {sql:variable("@sSerie")}
            else ()
            ,if (sql:variable("@sFolio") != "")
            then attribute folio {sql:variable("@sFolio")}
            else ()
        }
        </Comprobante>
        )
        into (/)[1]'
    )

Select @xmlCfd

"Bob"  escribió en el mensaje 
news:4E672E09-9EF5-46E7-96F1-A2337B311878@microsoft.com...
> Got these two to work, but using element:
>
> DECLARE @sSerie VARCHAR(10),
> @sFolio VARCHAR(10)
>
> DECLARE @xmlCfd XML
>
> SET @xmlCfd = ''
> SET @sSerie = 2 -- Or 3
> SET @sFolio = 3
>
> -- Go easy on yourself and check the condition in SQL
> IF @sSerie = 2
> SET @xmlCfd.modify('declare namespace cfd="http://www.sat.gob.mx/cfd/2";
>   insert (<Comprobante>{sql:variable("@sSerie")}</Comprobante>)
>   into (/)[1]')
> ELSE
> SET @xmlCfd.modify('declare namespace cfd="http://www.sat.gob.mx/cfd/2";
>   insert (<Comprobante>{sql:variable("@sFolio")}</Comprobante>)
>   into (/)[1]')
>
> SELECT @xmlCfd
>
> -- Reset
> -- XQuery if method
> SET @xmlCfd = ''
> SET @xmlCfd.modify('declare namespace cfd="http://www.sat.gob.mx/cfd/2";
> insert
> if (sql:variable("@sSerie")="2")
> then element Comprobante {sql:variable("@sSerie")}
> else element Comprobante {sql:variable("@sFolio")}
> into (/)[1]')
>
> SELECT @xmlCfd
>
> This returns:
> <Comprobante>2</Comprobante>
>
> I know you mentioned attributes, but you can't have valid xml with
> attributes but no elements right?
>
> Post your expected results if this doesn't help.
>
> HTH
> wBob
>
> "SammyBar" wrote:
>
>> Hi,
>>
>> I'm experimenting with XML DML. I need to add an attribute only if it 
>> meet
>> some condition. But I can not use an sql variable to implement the
>> condition. The following example gives me an error:
>> XQuery [modify()]: An expression was expected
>>
>> Declare @sSerie Varchar(10)
>>   ,@sFolio Varchar(10)
>>
>> Declare @xmlCfd xml
>> Set @xmlCfd = ''
>> Set @xmlCfd.modify('
>>   declare namespace cfd="http://www.sat.gob.mx/cfd/2";
>>   insert (
>>     <Comprobante>
>>       {
>>         if ({sql:variable("@sSerie")} = 2)
>>         then attribute serie {sql:variable("@sSerie")}
>>         else ()
>>         ,attribute folio {sql:variable("@sFolio")}
>>       }
>>     </Comprobante>
>>   )
>>   into (/)[1]'
>> )
>>
>> Select @xmlCfd
>>
>> Obviously, the condition I need should look like
>> if ({sql:variable("@sSerie")} != '')
>>
>> How to implement such an expression?
>>
>> Any hint is welcomed
>> Thanks in advance
>> Sammy
>>
>>
>>

date: Tue, 1 Jul 2008 18:09:43 -0500   author:   SammyBar