Login | Register   
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


advertisement
 

Kiss the Middle-tier Goodbye with SQL Server Yukon : Page 7

Yukon's new XML support features cut out the middleman and allow you to deal with your data directly on the database tier. Learn how to use these features to improve your database app's performance and design.


advertisement
The XML Data Modification Language (XML DML)
What if you want to alter XML data stored within Yukon? In SQL 200, you had to load the data from the database and pass it to a middle-tier component. The component then changed the data and wrote the changes back to the database.

Eliminating this step, Microsoft has extended the XQuery specification with the XML Data Modification Language. Based on XQuery, this language provides the necessary functionality to change XML data directly on the database tier.

Access the XML Data Modification Language using the XML data method modify(). The XML DML uses three keywords, insert, delete, and update.

  • insert: Take a look at the syntax of the insert keyword:


    insert Expression1 ( {as first | as last} into | after | before Expression2 )

    The following table describes each parameter:

    Parameter

    Description

    Expression1

    References the nodes that should be inserted. This can be an XQuery statement or a static XML fragment.

    into

    The XML nodes specified through Expression1 are inserted directly as children to the XML nodes specified by Expression2.

    as first

    Indicates that the nodes of Expression1 are inserted first into the nodes specified by Expression2.

    as last

    Indicates that the nodes of Expression1 are inserted last into the nodes specified by Expression2.

    after

    Indicates that the nodes of Expression1 are inserted directly after the nodes specified by Expression2 as siblings.

    before

    Indicates that the nodes of Expression1 are inserted directly before the nodes specified by Expression2 as siblings.

    Expression2

    Specifies the XML nodes in which the nodes from Expression1 should be inserted. This is an XQuery statement.

    The following code shows how to use the insert keyword:

    DECLARE @myDoc xml SET @myDoc = '<Root> <ProductDescription ProductID="1" ProductName="Road Bike"> <Features> </Features> </ProductDescription> </Root>' SELECT @myDoc SET @myDoc::modify('insert <Maintenance>3 years </Maintenance> into /Root/ProductDescription/ Features') SELECT @myDoc SET @myDoc::modifiy('insert <Warranty>1 year </Warranty> as first into /Root/ProductDescription/Features') SELECT @myDoc SET @myDoc::modify('insert <Material>Aluminum </Material> as last into /Root/ProductDescription/Features') SELECT @myDoc SET @myDoc::modify('insert <BikeFrame>Strong </BikeFrame> after /Root/ProductDescription/Features/Material') SELECT @myDoc

    The attribute attribute of the insert keyword allows you to insert new attributes into XML data:

    DECLARE @myDoc xml SET @myDoc = <Root> <WorkCenter WorkCenterID=10> <step>Step 1</step> <step>Step 2</step> </WorkCenter> </Root> SELECT @myDoc SET @myDoc::modify(insert attribute LaborHours {.5} into /Root/WorkCenter[@WorkCenterID=10]) SELECT @myDoc DECLARE @Hrs float SET @Hrs = .2 SET @myDoc::modify(insert attribute MachineHours {sql:variable(@Hrs)} into /Root/WorkCenter[@WorkCenterID=10]) SELECT @myDoc SET @myDoc::modify(insert ( attribute SetupHours {.5 }, attribute SomeOtherAtt {.2 } ) into /Root/WorkCenter[@WorkCenterID=10]) SELECT @myDoc

  • delete: This keyword, naturally, deletes nodes from your XML data. This keyword expects only one parameter: an XQuery statement that selects the nodes to be deleted. Here's how to use it:

    DECLARE @myDoc xml SET @myDoc = '<?Instructions for=TheWC.exe ?> <Root> <!-- Instructions for the first work center--> <WorkCenter WorkCenterID="10" LaborHours="1.1" MachineHours=".2"> Some text 1 <step>Step 1</step> <step>Step 2</step> </WorkCenter> </Root>' SELECT @myDoc SET @myDoc::modify('delete /Root/WorkCenter/step[2]') SELECT @myDoc SET @myDoc::modify('delete /Root/WorkCenter/@MachineHours') SELECT @myDoc SET @myDoc::modify('delete /Root/WorkCenter/text()') SELECT @myDoc SET @myDoc::modify('delete //processing-instruction()') SELECT @myDoc

  • update: As to be expected, this keyword updates XML data nodes:

    update [value of] Expression1 to Expression2

    The following table describes each parameter in more detail:

    Parameter

    Description

    Expression1

    Specifies the XML node that should be updated. The specified XQuery statement must return only 1 node. If 0 or more than 1 nodes are returned an exception is raised.

    Expression2

    Specifies the new value of the node specified by Expression1. This can be also an XQuery statement.

    [value of]

    Optional parameter. If this parameter is used then Expression2 is not treated as an XQuery statement, but as plain XML data.

    The following code shows how to use this keyword:

    DECLARE @myDoc xml SET @myDoc = '<Root> <WorkCenter WorkCenterID="10" LaborHours="1.1" MachineHours=".2"> Manu steps are described here <step>Step 1</step> <step>Step 2</step> </WorkCenter> </Root>' SELECT @myDoc SET @myDoc::modify('update /Root/WorkCenter/step[1]/text() to "new text describing the manu step"') SELECT @myDoc SET @myDoc::modify('update /Root/WorkCenter/@LaborHours to "100.0"') SELECT @myDoc



Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap