Prev: how to strip specific email address from nvarchar field with multi
Next: Query running slow
From: SammyBar on 1 Jul 2008 15:17
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


From: Bob on 1 Jul 2008 16:46
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
>
>
>
From: SammyBar on 1 Jul 2008 19:09
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" <Bob(a)discussions.microsoft.com> escribi� en el mensaje
news:4E672E09-9EF5-46E7-96F1-A2337B311878(a)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
>>
>>
>>


 | 
Pages: 1
Prev: how to strip specific email address from nvarchar field with multi
Next: Query running slow