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

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