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
运行结果: