SQL XML operations

Generate XML data for the table


SelectSiteid,GoodsareaFrom 
TableFor
XML Path

<Row>
<Siteid> 0101 </siteid>
<Goodsarea> A </goodsarea>
</Row>
<Row>
<Siteid> 0109 </siteid>
<Goodsarea> A </goodsarea>
</Row>

</Row>

Convert XML into a table

Method 1 nodes Function

Declare @ XML
Set @ xml ='
<Polist>
<Po ponumber = "100" podate = "2008-09-10"/>
<Po ponumber = "101" podate = "2008-09-11"/>
</Polist>'
Select
Doc. Col. Value ('@ ponumber', 'varchar (10)') ponumber
, Doc. Col. Value ('@ podate', 'datetime') podate
From @ XML. nodes ('/polist/po') Doc (COL)

DOC (COL) Table field alias

 
Method 2 openxml Functions
 

Column in the help document

 declare @ IDOC intdeclare @ Doc varchar (1000) set @ Doc = '
   
    
     
      
      
     
    
    
     
      
     
    
   '-- create an internal representation of the XML document. exec sp_xml_preparedocument @ IDOC output, @ doc -- execute a SELECT statement that uses the openxml rowset provider. select * From openxml (@ IDOC, '/root/customer', 1) with (customerid varchar (10), contactname varchar (20) 

Finally, sp_xml_removedocument indicates the XML document from the cache.

 
Exec sp_xml_removedocument @ hdoc -- the handle of the new document. Invalid handle will return an error. Hdoc is an integer