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.
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 xmlSET
@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 xmlSET
@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
@myDocGO
/*
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 xmlSET
@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
@myDocTo 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.