sql xml order processor

Question

I was wondering if anyone could point me to a tutorial or give me suggestions on processing an XML "Request For Quote" document. Using SQL Server 2005. For example:

<MyQuote>  
<Header>  
  <Customer>XYZ</Customer>  
  <Material>Wood&</Material>  
<Header>  
<LineItems>  
<Line>  
   <Quantity>32</Quantity>  
   <Model>Chair 350</Model>  
   <Color>Red</Color>    
 </Line>  
  .   
</LineItems>  
</MyQuote>

Just getting my feet wet with XQuery. I need to shred this doc into individual Line Items, identify each one with a number, validate them calc a price. Also need to be able to add/edit/delete any one. I can see how to get the lines into a table, but not how to ID them with a sequential number and apply a Stored Procedure to them. I need to randomly be able to process any request such as validate line 6, calc price for line 3 delete line 4, change line 2 to Yellow, etc.

I'm assuming the best way is to store the entire document as an XML column and transact against it as opposed to shredding and storing individual line items in a relational table?

Any advice, code, urls, etc would be greatly appreciated.

Answer 1

I was wondering if anyone could point me to a tutorial or give me suggestions on processing an XML "Request For Quote" document. Using SQL Server 2005

Here is some useful pages:

• Introduction to XQuery in SQL Server 2005
• XML Data Modification Language (XML DML)
• xml Data Type Methods
• XQuery Language Reference (Database Engine)

I need to shred this doc into individual Line Items

This will give you all lines:

select 
  T.N.value('Quantity[1]', 'int') as Quantity,
  T.N.value('Model[1]', 'varchar(25)')    as Model,
  T.N.value('Color[1]', 'varchar(25)')    as Color
from @XML.nodes('/MyQuote/LineItems/Line') as T(N)

each one with a number

Not so easy. Have a look at this answer. XQuery and Node Ids

Also need to be able to add/edit/delete any one

Just use modify() Method (xml Data Type)

I need to randomly be able to process any request such as validate line 6, calc price for line 3 delete line 4, change line 2 to Yellow

To get line 6 you should to this:

select 
  T.N.value('Quantity[1]', 'int') as Quantity,
  T.N.value('Model[1]', 'varchar(25)')    as Model,
  T.N.value('Color[1]', 'varchar(25)')    as Color
from @XML.nodes('/MyQuote/LineItems/Line[6]') as T(N)

/MyQuote/LineItems/Line[6] is the same as /MyQuote/LineItems/Line[position()=6]

I'm assuming the best way is to store the entire document as an XML column and transact against it as opposed to shredding and storing individual line items in a relational table?

Don't know what is best for you but I would definitely shred the data to tables unless there where some really compelling reasons not to.