使用SQL解析XML 注意事项

XML类型的数据，目前就我所知道的是在SSB中使用，做数据异步处理，可以做一些业务处理，一下列出一些提高Query的效率

示例：

One: nodes 和 value

DECLARE @XMLVALUE XML
SET @XMLVALUE = N'<ROOT>
 <PERSON>
  <ONEPERSON>
   <ID>1</ID>
   <NAME>Dylan</NAME>
  </ONEPERSON>
 </PERSON>
</ROOT>'

--第一种方式

SELECT T.C.value('(ID/text())[1]','char(5)'),T.C.value('(NAME/text())[1]','char(6)')
 from @XMLVALUE.nodes('/ROOT/PERSON/ONEPERSON') T(C)

--第二种方式

SELECT  @XMLVALUE.value('(/ROOT/PERSON/ONEPERSON/ID/text())[1]','CHAR(10)'),
  @XMLVALUE.value('(/ROOT/PERSON/ONEPERSON/NAME/text())[1]','CHAR(10)')

执行上面的例子，查看Execution Plan,可以发现第二种比第一种的效率高，

结论:在只包含单一值的节点中，我们没有必要使用nodes() ，以避免额外的开销。这种情况在给变量赋值时，需特别关注，因为变量里保存某个值，完全没有必要使用nodes()。

 

two: value和exist

经常会判断某个字段的值等于什么,下面的示例演示了使用value和exist

SELECT 'OK' WHERE @XMLVALUE.exist('/ROOT/PERSON/ONEPERSON/ID[text()=1]')=1
SELECT  'OK' WHERE @XMLVALUE.value('(/ROOT/PERSON/ONEPERSON/ID/text())[1]','CHAR(1)')='1'

曾经很大一部分时间，我自信地认为exist() 方法 要比 value()方法来判断某个条件时更有效，性能更高，但是，几次实践证明），使用value()方法，要比exist() 快许多，我们先前为什么要那么相信exist()更好呢? 因为是微软相关文档告诉我们exist()性能比较高。看来我们不能太相信微软，得靠实践。

Three:

local-name () 函数是用来获取节点名的，当XML的节点不固定时，我们可能不能判断某个节点是否是我们需要的，那么就会想到用local-name () 配合全配符 * 来处理，在特殊的XML里，这个方法很实用。但是当XML是确定的，使用这个函数，性能将有很大的牺牲。依然举例说明：
第一种：

SELECT @XMLVALUE.value('(/*[local-name()="ROOT"]/*[local-name()="PERSON"]/*[local-name()="ONEPERSON"]/*[local-name()="ID"]/text())[1]','char(5)') as XIXI
第二种
SELECT  @XMLVALUE.value('(/ROOT/PERSON/ONEPERSON/ID/text())[1]','CHAR(10)') as XIXI

使用local-name() 消耗100%，而另外一种只消耗接近0%。

结论:一般地，我们在处理一个确定的XML时，不能使用local-name()。

 

Four:关于命名空间

DECLARE @XMLVALUE XML
SET @XMLVALUE = N'<ROOT xmlns="https://soa.demo.com/">
 <PERSON>
  <ONEPERSON>
   <ID xmlns="https://soa.demo1.com1%22%3e1%3c/id>
   <NAME>Dylan</NAME>
  </ONEPERSON>
 </PERSON>
</ROOT>'

--一般使用:
 ;with xmlnamespaces(DEFAULT 'https://soa.demo.com')
SELECT  @XMLVALUE.value('(/ROOT/PERSON/ONEPERSON/ID/text())[1]','CHAR(10)'),
  @XMLVALUE.value('(/ROOT/PERSON/ONEPERSON/NAME/text())[1]','CHAR(10)')

而不使用:

SELECT @XMLVALUE.value('(/*:ROOT/*:PERSON/*:ONEPERSON/*:ID/text())[1]','CHAR(5)')

 

 

Five:如果出现几个相同的节点，就只有使用循环：

 

DECLARE @XMLVALUE XML
SET @XMLVALUE = N'<ROOT>
 <PERSON>
  <COUNT>2</COUNT>
  <ONEPERSON>
   <ID>1</ID>
   <NAME>Dylan</NAME>
  </ONEPERSON>
  <ONEPERSON>
   <ID>2</ID>
   <NAME>Dylan2</NAME>
  </ONEPERSON>
 </PERSON>
</ROOT>'

 DECLARE @ITEMCOUNT INT,
   @NAME CHAR(10),
   @ID INT
 SELECT @ITEMCOUNT=@XMLVALUE.value('(/ROOT/PERSON/COUNT/text())[1]','int')
 WHILE(@ITEMCOUNT > 0)
 BEGIN
  SELECT @ID = T.C.value('(ONEPERSON[sql:variable("@ITEMCOUNT")]/ID/text())[1]','int'),
    @NAME = T.C.value('(ONEPERSON[sql:variable("@ITEMCOUNT")]/NAME/text())[1]','CHAR(10)')
        from @XMLVALUE.nodes('/ROOT/PERSON') T(C)
 select @NAME AS NAME,@ID AS ID
-- SELECT @ID = @XMLVALUE.value('(/ROOT/PERSON/ONEPERSON[sql:variable("@ITEMCOUNT")]/ID/text())[1]','int')
--  SELECT @NAME = @XMLVALUE.value('(/ROOT/PERSON/ONEPERSON[sql:variable("@ITEMCOUNT")]/NAME/text())[1]','CHAR(10)')
--  select @NAME AS NAME,@ID AS ID
    SET @ITEMCOUNT = @ITEMCOUNT-1
 END

 

以上例子就举例说明了在XML中出现的多个子节点，如何用SQL去捞取数据，

 

 

原创！ 转载请说明出处，并请告知我！ 谢谢！