xml.modify insert with sql:variable

[Prakash V]

Hi,

I have a stored procedure which gets an xml string and a Id

i have to insert the xml into stuctured xml column with in the element with
the given id.

I tried like this, but i got an error -- XQuery
[HSStructure.XMLStructure.modify()]: An expression was expected.

Declare @var xml,@Id int

Set @var =N'<Node Id="2" Type="1">

<Node Id="3" Type="2">

</Node>

</Node>'

Set @count=1

UPDATE TBL_Structure

SET Structure.modify('insert {sql:variable("@var")} into
//Node[@Id="{sql:variable("@Id")}"]')

What i have to do it to make it work.

Thanks,

Prakash.V

[Han]

Hello

You can't insert the variable that way when the variable is XML. There are
only some workarounds.

1. Compose whole SQL dynamically and EXEC it.

2. Compose your XML with Xquery. Example,

@x='<a><b/></a>'
@y='<c/>'
@z=(select @x.query('/*/*'), @y for xml path)

3. Use Xquery within expression-1 below.

modify( insert expression-1 expression-2 )

Problem is you can apply xquery only against current XML instance, as far as
I know. So workaround is first attach the second XML and modify. Example,

@z=(select @x, @y)
@z.modify('insert element a {/*[last()]} into (/*)[1]')

Post by Prakash V
Hi,
I have a stored procedure which gets an xml string and a Id
i have to insert the xml into stuctured xml column with in the element
with the given id.
I tried like this, but i got an error -- XQuery
[HSStructure.XMLStructure.modify()]: An expression was expected.
<Node Id="3" Type="2">
</Node>
</Node>'
UPDATE TBL_Structure
What i have to do it to make it work.
Thanks,
Prakash.V

[Michael Rys [MSFT]]

This is an often encountered limitation. Please go and file/vote on this
issue at http://connect.microsoft.com/sqlserver.

Best regards
Michael

Post by Prakash V
Hi,
I have a stored procedure which gets an xml string and a Id
i have to insert the xml into stuctured xml column with in the element
with the given id.
I tried like this, but i got an error -- XQuery
[HSStructure.XMLStructure.modify()]: An expression was expected.
<Node Id="3" Type="2">
</Node>
</Node>'
UPDATE TBL_Structure
What i have to do it to make it work.
Thanks,
Prakash.V

[Prakash V]

Thanks for your information.

I need to try some alternative.

Thanks,
prakash.V

Post by Michael Rys [MSFT]
This is an often encountered limitation. Please go and file/vote on this
issue at http://connect.microsoft.com/sqlserver.
Best regards
Michael

Post by Prakash V
Hi,
I have a stored procedure which gets an xml string and a Id
i have to insert the xml into stuctured xml column with in the element
with the given id.
I tried like this, but i got an error -- XQuery
[HSStructure.XMLStructure.modify()]: An expression was expected.
<Node Id="3" Type="2">
</Node>
</Node>'
UPDATE TBL_Structure
What i have to do it to make it work.
Thanks,
Prakash.V

[Michael Rys [MSFT]]

The recommended workaround today is to use dynamic SQL....

Best regards
Michael

Post by Prakash V
Thanks for your information.
I need to try some alternative.
Thanks,
prakash.V

Post by Michael Rys [MSFT]
This is an often encountered limitation. Please go and file/vote on this
issue at http://connect.microsoft.com/sqlserver.
Best regards
Michael

Post by Prakash V
Hi,
I have a stored procedure which gets an xml string and a Id
i have to insert the xml into stuctured xml column with in the element
with the given id.
I tried like this, but i got an error -- XQuery
[HSStructure.XMLStructure.modify()]: An expression was expected.
<Node Id="3" Type="2">
</Node>
</Node>'
UPDATE TBL_Structure
What i have to do it to make it work.
Thanks,
Prakash.V