For a long time, I want to write a summary of the common methods for SQL Server to parse XML data. The following are some examples. If something is okay, you can refer to the examples and try to implement your own needs.
Example 1: parse data from XML to table Variables
Declare @ Itemmessage XML
Declare @ Itemtable Table (Itemnumber Int Primary Key , Itemdescription Nvarchar ( 300 ))
Set @ Itemmessage = N ' <Itemlist>
<Item>
<Itemnumber> 1 </itemnumber>
<Itemdescription> Xbox 360, great value </itemdescription>
</Item>
<Item>
<Itemnumber> 2 </itemnumber>
<Itemdescription> Windows phone7, come and have a taste </itemdescription>
</Item>
</Itemlist> '
Insert Into @ Itemtable
(
Itemnumber,
Itemdescription
)
Select T. C. Value ( ' (Itemnumber/text () [1] ' , ' Int ' ),
T. C. Value ( ' (Itemdescription/text () [1] ' , ' Nvarchar (300) ' )
From @ Itemmessage . Nodes ( ' /Itemlist/item ' ) As T (c)
Select Itemnumber,
Itemdescription
From @ Itemtable
Running result:
Example 2: Parse XML data with namespace
Declare @ Itemmessage XML
Declare @ Itemtable Table (Itemnumber Int Primary Key , Itemdescription Nvarchar ( 300 ))
Set @ Itemmessage = N ' <Itemlist xmlns = "http://cd.love.com/SOA">
<Item>
<Itemnumber> 1 </itemnumber>
<Itemdescription> Xbox 360, great value </itemdescription>
</Item>
<Item>
<Itemnumber> 2 </itemnumber>
<Itemdescription> Windows phone7, come and have a taste </itemdescription>
</Item>
</Itemlist> '
; With Xmlnamespaces ( Default ' Http://cd.love.com/SOA ' )
Insert Into @ Itemtable
(
Itemnumber,
Itemdescription
)
Select T. C. Value ( ' (Itemnumber/text () [1] ' , ' Int ' ),
T. C. Value ( ' (Itemdescription/text () [1] ' , ' Nvarchar (300) ' )
From @ Itemmessage . Nodes ( ' /Itemlist/item ' ) As T (c)
Select Itemnumber,
Itemdescription
From @ Itemtable
Running result:
