sql server 解析xml

实例一：sql server 2000 解析xml

DECLARE @HDOC INT    --文档句柄  
DECLARE @XMLSTRING VARCHAR(200)        --XML字符串  
SET @xmlString ='<?xmlversion="1.0"?>  
<ROOT>  
    <USERID="1"Name="SBQCEL"/>  
    <USERID="2"Name="PEACELI"/>  
    <USERID="3"Name="SHEEPCHANG"/>  
</ROOT>'  
--使用系统存储过程SP_XML_PREPAREDOCUMENT分析XML字符串  
EXEC SP_XML_PREPAREDOCUMENT @HDOC OUTPUT, @xmlString  
--使用OPENXML从SQL Server 的内部缓存查询数据  
SELECT * FROM OPENXML(@HDOC,N'/ROOT/USER')  
WITH   
(   
    ID INT,  
    Name VARCHAR(10)  
)  
--使用系统存储过程SP_XML_REMOVEDOCUMENT释放内存  
EXEC SP_XML_REMOVEDOCUMENT @HDOC

 

DECLARE @xmldata_id int 
DECLARE @xmldata VARCHAR(8000)
SET @xmldata='<root>
    <Customer>
        <customerid>1</customerid>
        <name>a</name>
    </Customer>
    <Customer>
        <customerid>2</customerid>
        <name>b</name>
    </Customer>
    </root>'
EXEC sp_xml_preparedocument @xmldata_id OUTPUT, @xmldata, '' 
SELECT  customerid,  [name] FROM 
OPENXML(@xmldata_id, '//Customer', 2) WITH (customerid int, [name] varchar(50)) 
EXEC sp_xml_removedocument @xmldata_id 

 

 

示例二：从XML中解析数据到表变量

DECLARE @ItemMessage XML 
DECLARE @ItemTable TABLE(ItemNumber INT PRIMARY KEY,ItemDescription NVARCHAR(300))
SET @ItemMessage=N'<ItemList> 
<Item> 
    <ItemNumber>1</ItemNumber> 
    <ItemDescription>XBox 360,超值</ItemDescription> 
</Item> 
<Item> 
    <ItemNumber>2</ItemNumber> 
    <ItemDescription>Windows Phone7,快来尝鲜吧</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

 

运行结果：

示例二： 解析带命名空间的XML数据

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,超值</ItemDescription> 
</Item> 
<Item> 
    <ItemNumber>2</ItemNumber> 
    <ItemDescription>Windows Phone7,快来尝鲜吧</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

 

运行结果：