使用SQL Server的OpenXML解析带有命名空间的XML片段
SQL Server的OpenXML函数可以针对XML片段或者文档进行解析,并处理。有关资料,你可以参考http://msdn.microsoft.com/en-us/library/ms186918.aspx
但如果该片段含有命名空间,情况可能会复杂一点。例如下面这个例子
第一部分:XML的内容
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
第二部分:查询语法
1
DECLARE @XML XML
2
DECLARE @HANDLER INT
3
SELECT @XML=CatalogDescription FROM Production.ProductModel WHERE ProductModelID=19
4![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
5
EXEC SP_XML_PREPAREDOCUMENT @HANDLER OUTPUT,@XML,'<root xmlns:p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription" />'
6![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
7
--这里要为PREPAREDOCUMENT存储过程加入第三个参数,就是命名空间的声明
8![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
9
SELECT * FROM OPENXML(@HANDLER,'/p1:ProductDescription/p1:Manufacturer',2) WITH ([Name] NVARCHAR(50) 'p1:Name',CopyRight NVARCHAR(50) 'p1:Copyright')
10![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
11
EXEC SP_XML_REMOVEDOCUMENT @HANDLER --一定不要忘记REMOVE
12![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
2
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
3
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
4
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
5
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
6
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
7
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
8
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
9
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
10
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
11
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
12
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
第三部分:显示结果(这是我写的一个小的演示工具)