10月25日

sql:variable() binding and modify method of xquery: insert XML DML

/*

In SQL Server 2005, we can use sql:variable and sql:column function to bind relational data into XML by means of xquery. However, the binding has special syntax and it is easy to be confused.

1) insert ELEMENT:

The element text can be bind to an sql variable:

*/

USE AdventureWorks;

GO

DECLARE

@myDoc xml

SET

@myDoc = '<Root>

<ProductDescription ProductID="1" ProductName="Road Bike">

<Features>

</Features>

</ProductDescription>

</Root>'

DECLARE

@v nvarchar(50),@v1 nvarchar(50), @Maintenance nvarchar(50)

SET

@Maintenance=N'No Maintenance Required'

SET

@myDoc.modify('

insert <Maintenance>{"Refill Water As Needed"}</Maintenance> as last

into (/Root/ProductDescription/Features)[1]'

)

SET

@myDoc.modify('

insert <Maintenance>Refill Gas As Needed</Maintenance> as last

into (/Root/ProductDescription/Features)[1]'

)

SET

@myDoc.modify('

insert <Maintenance>{sql:variable("@Maintenance")}</Maintenance> as last

into (/Root/ProductDescription/Features)[1]'

)

SET

@Maintenance=N'Oil Required Every Month'

SET

@myDoc.modify('

insert <Maintenance>{sql:variable("@Maintenance")}</Maintenance> as last

into (/Root/ProductDescription/Features)[1]'

)

SELECT

@myDoc

/*

The syntax is to use {sql:variable("@sqlvariable")}. Note if a constant value is used, the '{"' and '"}' pair showed in the first sample can be igored, as showed in the second one, which is the most frequently used syntax for constant.

The '{"' must go together. You can not have '{' without double quotes. SQL Server may insert a blank element or fail. If the constant has spaces, it will fail.

e.g.: the following will insert a empty <Maintenance> element. However if you change {Refill} to {Refill Water}, it will fail.

*/

DECLARE

@myDoc xml

SET

@myDoc = '<Root>

<ProductDescription ProductID="1" ProductName="Road Bike">

<Features>

</Features>

</ProductDescription>

</Root>'

SET

@myDoc.modify('

insert <Maintenance>{Refill}</Maintenance> as last

into (/Root/ProductDescription/Features)[1]'

)

SELECT

@myDoc

GO

/*

2)  insert ATTRIBUTE

The syntax is {sql:variable("@sql_variable")}. If the value is a constant and it does not have spaces,

the double quotes pair can be ommited. The bracket '{' pair can never be ommited. If you change the attribute PartList {"P1 P2 P3"} to attribute PartList {P1 P2 P3}, it will fail

*/

DECLARE

@myDoc xml

SET

@myDoc = '<Root>

<ProductDescription ProductID="1" ProductName="Road Bike">

<Features>

</Features>

</ProductDescription>

</Root>'

DECLARE

@v nvarchar(50),@v1 nvarchar(50)

SELECT

@v='20',@v1='M001'

SET

@myDoc.modify('insert ( attribute PartList {"P1 P2 P3"},

attribute PriceSuggested {100000.00},

attribute LaborHours {sql:variable("@v")},

attribute Code {sql:variable("@v1")})

into (/Root/ProductDescription)[1]'

)

SELECT

@myDoc

To summary, always use {sql:variable("@sql_variable")} syntax in insert XML DML regardless for elements or attributes. For constant, type the constant directly as the text of the element and enclose the constant in {" "} pair for attributes.