SqlServer的xml操作， SQL XML 查询出错：'value()' 需要单独的操作数(或空序列)，但找到 'xdt:anyAtomicType *' 类型的操作数

于 2020-04-28 16:32:05 发布
阅读量1k 收藏 3
点赞数
分类专栏： 数据库 经验分享 文章标签： sql xml 数据结构 sqlserver 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/sunny_day_day/article/details/105815737
版权

SQL XML 查询出错：‘value()’ 需要单独的操作数(或空序列)，但找到 ‘xdt:anyAtomicType *’ 类型的操作数

SQL XML 查询出错：‘value()’ 需要单独的操作数(或空序列)，但找到 ‘xdt:anyAtomicType *’ 类型的操作数

[Err] 42000 - [SQL Server]XQuery [dbo.skudata .sku.value()]: ‘value()’ 需要单独的操作数(或空序列)，但找到 ‘xdt:untypedAtomic *’ 类型的操作数

SQL Server 中对XML数据的五种基本操作

### 1.xml.exist
   输入为XQuery表达式，返回0，1或是Null。0表示不存在，1表示存在，Null表示输入为空
### 2.xml.value
   输入为XQuery表达式，返回一个SQL Server标量值
### 3.xml.query
   输入为XQuery表达式，返回一个SQL Server XML类型流
### 4.xml.nodes
   输入为XQuery表达式，返回一个XML格式文档的一列行集
### 5.xml.modify
使用XQuery表达式对XML的节点进行insert , update 和 delete 操作。

源表数据结构

id（bigint）sku (xml格式存储)
1131076662075723776<List><SkuInfos><skuName>果倍爽200橙</skuName><layerDetail>第6层</layerDetail><unit>包</unit><isCompete>0</isCompete><series>果汁饮料</series><layerSet class=""com.alibaba.fastjson.JSONArray""><List><int>6</int></List></layerSet><skuCount>2</skuCount></SkuInfos><SkuInfos><skuName>红牛原味</skuName><layerDetail>第1,2,3,4,5层</layerDetail><unit>罐</unit><isCompete>0</isCompete><series>运动饮料</series><layerSet class=""com.alibaba.fastjson.JSONArray""><List><int>1</int><int>2</int><int>3</int><int>4</int><int>5</int></List></layerSet><skuCount>47</skuCount></SkuInfos><SkuInfos><skuName>果倍爽200梨</skuName><layerDetail>第6层</layerDetail><unit>包</unit><isCompete>0</isCompete><series>果汁饮料</series><layerSet class=""com.alibaba.fastjson.JSONArray""><List><int>6</int></List></layerSet><skuCount>4</skuCount></SkuInfos><SkuInfos><skuName>战马310ML-罐装</skuName><layerDetail>第7层</layerDetail><unit>罐</unit><isCompete>0</isCompete><series>运动饮料</series><layerSet class=""com.alibaba.fastjson.JSONArray""><List><int>7</int></List></layerSet><skuCount>7</skuCount></SkuInfos></List>
1131116486354669568<List><SkuInfos><skuName>果倍爽200橙</skuName><layerDetail>第6层</layerDetail><unit>包</unit><isCompete>0</isCompete><series>果汁饮料</series><layerSet class=""com.alibaba.fastjson.JSONArray""><List><int>6</int></List></layerSet><skuCount>2</skuCount></SkuInfos><SkuInfos><skuName>红牛原味</skuName><layerDetail>第1,2,3,4,5层</layerDetail><unit>罐</unit><isCompete>0</isCompete><series>运动饮料</series><layerSet class=""com.alibaba.fastjson.JSONArray""><List><int>1</int><int>2</int><int>3</int><int>4</int><int>5</int></List></layerSet><skuCount>47</skuCount></SkuInfos><SkuInfos><skuName>果倍爽200梨</skuName><layerDetail>第6层</layerDetail><unit>包</unit><isCompete>0</isCompete><series>果汁饮料</series><layerSet class=""com.alibaba.fastjson.JSONArray""><List><int>6</int></List></layerSet><skuCount>4</skuCount></SkuInfos><SkuInfos><skuName>战马310ML-罐装</skuName><layerDetail>第7层</layerDetail><unit>罐</unit><isCompete>0</isCompete><series>运动饮料</series><layerSet class=""com.alibaba.fastjson.JSONArray""><List><int>7</int></List></layerSet><skuCount>7</skuCount></SkuInfos></List>
1131116486564384768<List><SkuInfos><skuName>果倍爽200橙</skuName><layerDetail>第6层</layerDetail><unit>包</unit><isCompete>0</isCompete><series>果汁饮料</series><layerSet class=""com.alibaba.fastjson.JSONArray""><List><int>6</int></List></layerSet><skuCount>2</skuCount></SkuInfos><SkuInfos><skuName>红牛原味</skuName><layerDetail>第1,2,3,4,5层</layerDetail><unit>罐</unit><isCompete>0</isCompete><series>运动饮料</series><layerSet class=""com.alibaba.fastjson.JSONArray""><List><int>1</int><int>2</int><int>3</int><int>4</int><int>5</int></List></layerSet><skuCount>47</skuCount></SkuInfos><SkuInfos><skuName>果倍爽200梨</skuName><layerDetail>第6层</layerDetail><unit>包</unit><isCompete>0</isCompete><series>果汁饮料</series><layerSet class=""com.alibaba.fastjson.JSONArray""><List><int>6</int></List></layerSet><skuCount>4</skuCount></SkuInfos><SkuInfos><skuName>战马310ML-罐装</skuName><layerDetail>第7层</layerDetail><unit>罐</unit><isCompete>0</isCompete><series>运动饮料</series><layerSet class=""com.alibaba.fastjson.JSONArray""><List><int>7</int></List></layerSet><skuCount>7</skuCount></SkuInfos></List>
1131170467093286912<List><SkuInfos><skuName>果倍爽200橙</skuName><layerDetail>第6层</layerDetail><unit>包</unit><isCompete>0</isCompete><series>果汁饮料</series><layerSet class=""com.alibaba.fastjson.JSONArray""><List><int>6</int></List></layerSet><skuCount>2</skuCount></SkuInfos><SkuInfos><skuName>红牛原味</skuName><layerDetail>第1,2,3,4,5层</layerDetail><unit>罐</unit><isCompete>0</isCompete><series>运动饮料</series><layerSet class=""com.alibaba.fastjson.JSONArray""><List><int>1</int><int>2</int><int>3</int><int>4</int><int>5</int></List></layerSet><skuCount>47</skuCount></SkuInfos><SkuInfos><skuName>果倍爽200梨</skuName><layerDetail>第6层</layerDetail><unit>包</unit><isCompete>0</isCompete><series>果汁饮料</series><layerSet class=""com.alibaba.fastjson.JSONArray""><List><int>6</int></List></layerSet><skuCount>4</skuCount></SkuInfos><SkuInfos><skuName>战马310ML-罐装</skuName><layerDetail>第7层</layerDetail><unit>罐</unit><isCompete>0</isCompete><series>运动饮料</series><layerSet class=""com.alibaba.fastjson.JSONArray""><List><int>7</int></List></layerSet><skuCount>7</skuCount></SkuInfos></List>
1131170468687122432<List><SkuInfos><skuName>果倍爽200橙</skuName><layerDetail>第6层</layerDetail><unit>包</unit><isCompete>0</isCompete><series>果汁饮料</series><layerSet class=""com.alibaba.fastjson.JSONArray""><List><int>6</int></List></layerSet><skuCount>2</skuCount></SkuInfos><SkuInfos><skuName>红牛原味</skuName><layerDetail>第1,2,3,4,5层</layerDetail><unit>罐</unit><isCompete>0</isCompete><series>运动饮料</series><layerSet class=""com.alibaba.fastjson.JSONArray""><List><int>1</int><int>2</int><int>3</int><int>4</int><int>5</int></List></layerSet><skuCount>47</skuCount></SkuInfos><SkuInfos><skuName>果倍爽200梨</skuName><layerDetail>第6层</layerDetail><unit>包</unit><isCompete>0</isCompete><series>果汁饮料</series><layerSet class=""com.alibaba.fastjson.JSONArray""><List><int>6</int></List></layerSet><skuCount>4</skuCount></SkuInfos><SkuInfos><skuName>战马310ML-罐装</skuName><layerDetail>第7层</layerDetail><unit>罐</unit><isCompete>0</isCompete><series>运动饮料</series><layerSet class=""com.alibaba.fastjson.JSONArray""><List><int>7</int></List></layerSet><skuCount>7</skuCount></SkuInfos></List>
1131170969830952960<List><SkuInfos><skuName>果倍爽200橙</skuName><layerDetail>第6层</layerDetail><unit>包</unit><isCompete>0</isCompete><series>果汁饮料</series><layerSet class=""com.alibaba.fastjson.JSONArray""><List><int>6</int></List></layerSet><skuCount>2</skuCount></SkuInfos><SkuInfos><skuName>红牛原味</skuName><layerDetail>第1,2,3,4,5层</layerDetail><unit>罐</unit><isCompete>0</isCompete><series>运动饮料</series><layerSet class=""com.alibaba.fastjson.JSONArray""><List><int>1</int><int>2</int><int>3</int><int>4</int><int>5</int></List></layerSet><skuCount>47</skuCount></SkuInfos><SkuInfos><skuName>果倍爽200梨</skuName><layerDetail>第6层</layerDetail><unit>包</unit><isCompete>0</isCompete><series>果汁饮料</series><layerSet class=""com.alibaba.fastjson.JSONArray""><List><int>6</int></List></layerSet><skuCount>4</skuCount></SkuInfos><SkuInfos><skuName>战马310ML-罐装</skuName><layerDetail>第7层</layerDetail><unit>罐</unit><isCompete>0</isCompete><series>运动饮料</series><layerSet class=""com.alibaba.fastjson.JSONArray""><List><int>7</int></List></layerSet><skuCount>7</skuCount></SkuInfos></List>
1131170970372018176<List><SkuInfos><skuName>果倍爽200橙</skuName><layerDetail>第6层</layerDetail><unit>包</unit><isCompete>0</isCompete><series>果汁饮料</series><layerSet class=""com.alibaba.fastjson.JSONArray""><List><int>6</int></List></layerSet><skuCount>2</skuCount></SkuInfos><SkuInfos><skuName>红牛原味</skuName><layerDetail>第1,2,3,4,5层</layerDetail><unit>罐</unit><isCompete>0</isCompete><series>运动饮料</series><layerSet class=""com.alibaba.fastjson.JSONArray""><List><int>1</int><int>2</int><int>3</int><int>4</int><int>5</int></List></layerSet><skuCount>47</skuCount></SkuInfos><SkuInfos><skuName>果倍爽200梨</skuName><layerDetail>第6层</layerDetail><unit>包</unit><isCompete>0</isCompete><series>果汁饮料</series><layerSet class=""com.alibaba.fastjson.JSONArray""><List><int>6</int></List></layerSet><skuCount>4</skuCount></SkuInfos><SkuInfos><skuName>战马310ML-罐装</skuName><layerDetail>第7层</layerDetail><unit>罐</unit><isCompete>0</isCompete><series>运动饮料</series><layerSet class=""com.alibaba.fastjson.JSONArray""><List><int>7</int></List></layerSet><skuCount>7</skuCount></SkuInfos></List>
1131381105899999232<List><SkuInfos><skuName>果倍爽200橙</skuName><layerDetail>第6层</layerDetail><unit>包</unit><isCompete>0</isCompete><series>果汁饮料</series><layerSet class=""com.alibaba.fastjson.JSONArray""><List><int>6</int></List></layerSet><skuCount>2</skuCount></SkuInfos><SkuInfos><skuName>红牛原味</skuName><layerDetail>第1,2,3,4,5层</layerDetail><unit>罐</unit><isCompete>0</isCompete><series>运动饮料</series><layerSet class=""com.alibaba.fastjson.JSONArray""><List><int>1</int><int>2</int><int>3</int><int>4</int><int>5</int></List></layerSet><skuCount>47</skuCount></SkuInfos><SkuInfos><skuName>果倍爽200梨</skuName><layerDetail>第6层</layerDetail><unit>包</unit><isCompete>0</isCompete><series>果汁饮料</series><layerSet class=""com.alibaba.fastjson.JSONArray""><List><int>6</int></List></layerSet><skuCount>4</skuCount></SkuInfos><SkuInfos><skuName>战马310ML-罐装</skuName><layerDetail>第7层</layerDetail><unit>罐</unit><isCompete>0</isCompete><series>运动饮料</series><layerSet class=""com.alibaba.fastjson.JSONArray""><List><int>7</int></List></layerSet><skuCount>7</skuCount></SkuInfos></List>
1131381230449856512<List><SkuInfos><skuName>果倍爽200橙</skuName><layerDetail>第6层</layerDetail><unit>包</unit><isCompete>0</isCompete><series>果汁饮料</series><layerSet class=""com.alibaba.fastjson.JSONArray""><List><int>6</int></List></layerSet><skuCount>2</skuCount></SkuInfos><SkuInfos><skuName>红牛原味</skuName><layerDetail>第1,2,3,4,5层</layerDetail><unit>罐</unit><isCompete>0</isCompete><series>运动饮料</series><layerSet class=""com.alibaba.fastjson.JSONArray""><List><int>1</int><int>2</int><int>3</int><int>4</int><int>5</int></List></layerSet><skuCount>47</skuCount></SkuInfos><SkuInfos><skuName>果倍爽200梨</skuName><layerDetail>第6层</layerDetail><unit>包</unit><isCompete>0</isCompete><series>果汁饮料</series><layerSet class=""com.alibaba.fastjson.JSONArray""><List><int>6</int></List></layerSet><skuCount>4</skuCount></SkuInfos><SkuInfos><skuName>战马310ML-罐装</skuName><layerDetail>第7层</layerDetail><unit>罐</unit><isCompete>0</isCompete><series>运动饮料</series><layerSet class=""com.alibaba.fastjson.JSONArray""><List><int>7</int></List></layerSet><skuCount>7</skuCount></SkuInfos></List>
1132202143785291776<List><SkuInfos><skuName>果倍爽200橙</skuName><layerDetail>第6层</layerDetail><unit>包</unit><isCompete>0</isCompete><series>果汁饮料</series><layerSet class=""com.alibaba.fastjson.JSONArray""><List><int>6</int></List></layerSet><skuCount>2</skuCount></SkuInfos><SkuInfos><skuName>红牛原味</skuName><layerDetail>第1,2,3,4,5层</layerDetail><unit>罐</unit><isCompete>0</isCompete><series>运动饮料</series><layerSet class=""com.alibaba.fastjson.JSONArray""><List><int>1</int><int>2</int><int>3</int><int>4</int><int>5</int></List></layerSet><skuCount>47</skuCount></SkuInfos><SkuInfos><skuName>果倍爽200梨</skuName><layerDetail>第6层</layerDetail><unit>包</unit><isCompete>0</isCompete><series>果汁饮料</series><layerSet class=""com.alibaba.fastjson.JSONArray""><List><int>6</int></List></layerSet><skuCount>4</skuCount></SkuInfos><SkuInfos><skuName>战马310ML-罐装</skuName><layerDetail>第7层</layerDetail><unit>罐</unit><isCompete>0</isCompete><series>运动饮料</series><layerSet class=""com.alibaba.fastjson.JSONArray""><List><int>7</int></List></layerSet><skuCount>7</skuCount></SkuInfos></List>

xml拆解目标表结构

idskuNamelayerDetailunitisCompeteskuCount
1131076662075723776果倍爽200橙第6层包本品2
1131076662075723776红牛原味第1,2,3,4,5 层罐本品47
1131076662075723776果倍爽200梨第6层包本品4
1131076662075723776战马310ML-罐装第7层罐本品7
1131116486354669568果倍爽200橙第6层包本品2
………
………
………

拆解sql—(单个结果的方式)

declare @xml1 xml
   SET  @xml1= (SELECT top 1 sku from dbo.skudata sra 
		WHERE sra.id=1132986306788462592
		FOR XML PATH(''))

	select t.c.value('(actualName/text())[1]','NVARCHAR(300)') as skuName,
		t.c.value('(layerDetail/text())[1]','NVARCHAR(300)') as layerDetail,
		t.c.value('(unit/text())[1]','NVARCHAR(300)') as unit,
		t.c.value('(isCompete/text())[1]','NVARCHAR(300)')as isCompete,
		t.c.value('(skuCount/text())[1]','NVARCHAR(300)') as skuCount
	from @xml1.nodes('sku/List/SkuInfos') as t(c)

错误的使用方式（批量）

SELECT top 1000 
sra.id,
--sra.sku,
sra.sku.value('(SkuName/text())[1]','NVARCHAR(300)') as skuName,
sra.sku.value('(layerDetail/text())[1]','NVARCHAR(300)') as layerDetail,
sra.sku.value('(unit/text())[1]','NVARCHAR(300)') as unit,
sra.sku.value('(isCompete/text())[1]','NVARCHAR(300)')as isCompete,
sra.sku.value('(skuCount/text())[1]','NVARCHAR(300)') as skuCount
 from dbo.skudata sra 

不能对xml进行直接拆解。

报错信息：
[Err] 42000 - [SQL Server]XQuery [dbo.skudata .sku.value()]: 'value()' 
需要单独的操作数(或空序列)，但找到 'xdt:untypedAtomic *' 类型的操作数

拆解sql—(批量方式)

SELECT top 1000 
sra.id,
sra.sku,
t.c.value('(SkuName/text())[1]','NVARCHAR(300)') as skuName,
		t.c.value('(layerDetail/text())[1]','NVARCHAR(300)') as layerDetail,
		t.c.value('(unit/text())[1]','NVARCHAR(300)') as unit,
		t.c.value('(isCompete/text())[1]','NVARCHAR(300)')as isCompete,
		t.c.value('(skuCount/text())[1]','NVARCHAR(300)') as skuCount
 from dbo.skudata sra 
cross apply [sku].nodes('List/skuInfos') t(c)

其他xml操作/查询sql示例

读取XML
--下面为多种方法从XML中读取e-mail
DECLARE @x XML
SELECT @x = '
<vipmenber>
  <menbers>
    <Info Name="e-mail">12345@qq.com</Info>
    <Info Name="Phone">9900000</Info>
    <Info Name="qq">100000</Info>
  </menbers>
</vipmenber>'
-- 方法1
SELECT @x.value('data(/vipmenber/menbers/Info[@Name="e-mail"])[1]', 'varchar(30)')
-- 方法2
SELECT @x.value('(/vipmenber/menbers/Info[@Name="e-mail"])[1]', 'varchar(30)')
-- 方法3
SELECT
  C.value('.','varchar(30)')
FROM @x.nodes('/vipmenber/menbers/Info[@Name="e-mail"]') T(C)
-- 方法4
SELECT
  C.value('(Info[@Name="e-mail"])[1]','varchar(30)')
FROM @x.nodes('/vipmenber/menbers') T(C)
-- 方法5
SELECT
  C.value('(menbers/Info[@Name="e-mail"])[1]','varchar(30)')
FROM @x.nodes('/vipmenber') T(C)
-- 方法6
SELECT
  C.value('.','varchar(30)')
FROM @x.nodes('/vipmenber/menbers/Info') T(C)
WHERE C.value('(.[@Name="e-mail"])[1]','varchar(30)') IS NOT NULL
-- 方法7
SELECT
  C.value('.','varchar(30)')
FROM @x.nodes('/vipmenber/menbers/Info') T(C)
WHERE C.exist('(.[@Name="e-mail"])[1]') = 1
--6.Reading values from an XML variable
DECLARE @x XML
SELECT @x =
'<vipmenbers>
  <vipmenber Name="tudou" Sex="F" />
  <vipmenber Name="choushuigou" Sex="F"/>
  <vipmenber Name="menbers" Sex="M" />
</vipmenbers>'
SELECT
  v.value('@Name[1]','VARCHAR(20)') AS Name,
  v.value('@Sex[1]','VARCHAR(20)') AS Sex
FROM @x.nodes('/vipmenbers/vipmenber') x(v)
--7.多属性过滤
DECLARE @x XML
SELECT @x = '
<checkworks>
 <checkwork id="1234" dept="IT" type="合同工">
  <Info NAME="menbers" SEX="M" QQ="5454545454"/>
 </checkwork>
 <checkwork id="5656" dept="IT" type="临时工">
  <Info NAME="FF013" SEX="F" QQ="5345454554"/>
 </checkwork>
 <checkwork id="3242" dept="市场" type="合同工">
  <Info NAME="choushuigou" SEX="F" QQ="54543545"/>
 </checkwork>
</checkworks>'
--查询dept为IT的人员信息
  --方法1
  SELECT
    C.value('@NAME[1]','VARCHAR(10)') AS NAME,
    C.value('@SEX[1]','VARCHAR(10)') AS SEX,
    C.value('@QQ[1]','VARCHAR(20)') AS QQ
  FROM @x.nodes('/checkworks/checkwork[@dept="IT"]/Info') T(C)
  /*
  NAME   SEX    QQ
  ---------- ---------- --------------------
  menbers M     5454545454
  FF013   F     5345454554
  */
  --方法2
  SELECT
    C.value('@NAME[1]','VARCHAR(10)') AS NAME,
    C.value('@SEX[1]','VARCHAR(10)') AS SEX,
    C.value('@QQ[1]','VARCHAR(20)') AS QQ
  FROM @x.nodes('//checkwork[@dept="IT"]/*') T(C)
  /*
  NAME   SEX    QQ
  ---------- ---------- --------------------
  menbers M     5454545454
  FF013   F     5345454554
  */
--查询出IT部门type为Permanent的员工
SELECT
  C.value('@NAME[1]','VARCHAR(10)') AS NAME,
  C.value('@SEX[1]','VARCHAR(10)') AS SEX,
  C.value('@QQ[1]','VARCHAR(20)') AS QQ
FROM @x.nodes('//checkwork[@dept="IT"][@type="合同工"]/*') T(C)
/*
  NAME   SEX    QQ
  ---------- ---------- --------------------
  menbers M     5454545454
*/
--12.从XML变量中删除元素
DECLARE @x XML
SELECT @x = '
<vipmenbers>
 <vipmenber>
   <NAME>FF013</NAME>
   <SEX>M</SEX>
   <QQ>5345454554</QQ>
 </vipmenber>
</vipmenbers>'
SET @x.modify('
  delete (/vipmenbers/vipmenber/SEX)[1]'
 )
SELECT @x
/*
<vipmenbers>
 <vipmenber>
  <NAME>FF013</NAME>
  <QQ>5345454554</QQ>
 </vipmenber>
</vipmenbers>
*/
--19.读取指定变量元素的值
DECLARE @x XML
SELECT @x = '
<vipmenbers>
 <vipmenber>
   <NAME>menbers</NAME>
   <SEX>M</SEX>
   <QQ>423545</QQ>
 </vipmenber>
 <vipmenber>
   <NAME>FF013</NAME>
   <SEX>M</SEX>
   <QQ>123133</QQ>
 </vipmenber>
 <vipmenber>
   <NAME>choushuigou</NAME>
   <SEX>F</SEX>
   <QQ>54543545</QQ>
 </vipmenber>
</vipmenbers>
'
DECLARE @ElementName VARCHAR(20)
SELECT @ElementName = 'NAME'
SELECT c.value('.','VARCHAR(20)') AS NAME
FROM @x.nodes('/vipmenbers/vipmenber/*[local-name()=sql:variable("@ElementName")]') T(C)
/*
NAME
--------------------
menbers
FF013
choushuigou
*/
--20使用通配符读取元素值
--读取根元素的值
DECLARE @x1 XML
SELECT @x1 = '<vipmenber>menbers</vipmenber>'
SELECT @x1.value('(/*/text())[1]','VARCHAR(20)') AS vipmenber --星号*代表一个元素
/*
vipmenber
--------------------
menbers
*/
--读取第二层元素的值
DECLARE  @x XML
SELECT @x = '
 <vipmenber>
   <NAME>menbers</NAME>
   <SEX>M</SEX>
   <QQ>423545</QQ>
 </vipmenber>'
SELECT
  @x.value('(/*/*/text())[1]','VARCHAR(20)') AS NAME
/*
NAME
--------------------
menbers
*/
--读取第二个子元素的值
DECLARE  @x XML
SELECT @x = '
 <vipmenber>
   <NAME>menbers</NAME>
   <SEX>M</SEX>
   <QQ>423545</QQ>
 </vipmenber>'
SELECT
  @x.value('(/*/*/text())[2]','VARCHAR(20)') AS SEX
/*
SEX
--------------------
M
*/
--读取所有第二层子元素值
DECLARE  @x XML
SELECT @x = '
 <vipmenber>
   <NAME>menbers</NAME>
   <SEX>M</SEX>
   <QQ>423545</QQ>
 </vipmenber>'
SELECT
  C.value('.','VARCHAR(20)') AS value
FROM @x.nodes('/*/*') T(C)
/*
value
--------------------
menbers
M
423545
*/
--21.使用通配符读取元素名称
DECLARE @x XML
SELECT @x = '<vipmenber>menbers</vipmenber>'
SELECT
  @x.value('local-name(/*[1])','VARCHAR(20)') AS ElementName
/*
ElementName
--------------------
vipmenber
*/
--读取根下第一个元素的名称和值
DECLARE  @x XML
SELECT @x = '
 <vipmenber>
   <NAME>menbers</NAME>
   <SEX>M</SEX>
 </vipmenber>'
SELECT
  @x.value('local-name((/*/*)[1])','VARCHAR(20)') AS ElementName,
  @x.value('(/*/*/text())[1]','VARCHAR(20)') AS ElementValue
/*
ElementName     ElementValue
-------------------- --------------------
NAME         menbers
*/
--读取根下第二个元素的名称和值
DECLARE  @x XML
SELECT @x = '
 <vipmenber>
   <NAME>menbers</NAME>
   <SEX>M</SEX>
 </vipmenber>'
SELECT
  @x.value('local-name((/*/*)[2])','VARCHAR(20)') AS ElementName,
  @x.value('(/*/*/text())[2]','VARCHAR(20)') AS ElementValue
/*
ElementName     ElementValue
-------------------- --------------------
SEX         M
*/
--读取根下所有的元素名称和值
DECLARE  @x XML
SELECT @x = '
 <vipmenber>
   <NAME>menbers</NAME>
   <SEX>M</SEX>
 </vipmenber>'
SELECT
  C.value('local-name(.)','VARCHAR(20)') AS ElementName,
  C.value('.','VARCHAR(20)') AS ElementValue
FROM @x.nodes('/*/*') T(C)
/*
ElementName     ElementValue
-------------------- --------------------
NAME         menbers
SEX         M
*/
---22.查询元素数量
--如下vipmenbers根节点下有个vipmenber子节点。
DECLARE @x XML
SELECT @x = '
<vipmenbers>
 <vipmenber>
   <NAME>menbers</NAME>
   <SEX>M</SEX>
 </vipmenber>
 <vipmenber>
   <NAME>FF013</NAME>
   <SEX>M</SEX>
 </vipmenber>
 <vipmenber>
   <NAME>choushuigou</NAME>
   <SEX>F</SEX>
 </vipmenber>
</vipmenbers>
'
SELECT  @x.value('count(/vipmenbers/vipmenber)','INT') AS Children
/*
Children
-----------
3
*/
--如下vipmenbers根节点下第一个子节点vipmenber下子节点的数量
SELECT  @x.value('count(/vipmenbers/vipmenber[1]/*)','INT') AS Children
/*
Children
-----------
2
*/
--某些时候我们可能不知道根节点和子节点的名称，可以用通配符来代替。
SELECT  @x.value('count(/*/*)','INT') AS ChildrenOfRoot,
     @x.value('count(/*/*[1]/*)','INT') AS ChildrenOfFirstChildElement
/*
ChildrenOfRoot ChildrenOfFirstChildElement
-------------- ---------------------------
3       2
*/
--23.查询属性的数量
DECLARE @x XML
SELECT @x = '
<checkworks dept="IT">
  <checkwork NAME="menbers" SEX="M" QQ="5454545454"/>
  <checkwork NAME="FF013" SEX="F" QQ="5345454554" TEL="18800009999"/>
</checkworks>'
--查询跟节点的属性数量
SELECT  @x.value('count(/checkworks/@*)','INT') AS AttributeCountOfRoot
/*
AttributeCountOfRoot
--------------------
1
*/
--第一个checkwork节点的属性数量
SELECT  @x.value('count(/checkworks/checkwork[1]/@*)','INT') AS AttributeCountOfFirstElement
/*
AttributeCountOfFirstElement
----------------------------
3
*/
--第二个checkwork节点的属性数量
SELECT  @x.value('count(/checkworks/checkwork[2]/@*)','INT') AS AttributeCountOfSeconfElement
/*
AttributeCountOfSeconfElement
-----------------------------
4
*/
--如果不清楚节点名称可以用*通配符代替
SELECT  @x.value('count(/*/@*)','INT') AS AttributeCountOfRoot
    ,@x.value('count(/*/*[1]/@*)','INT') AS AttributeCountOfFirstElement
    ,@x.value('count(/*/*[2]/@*)','INT') AS AttributeCountOfSeconfElement
/*
AttributeCountOfRoot AttributeCountOfFirstElement AttributeCountOfSeconfElement
-------------------- ---------------------------- -----------------------------
1          3              4
*/
--返回没个节点的属性值
SELECT  C.value('count(./@*)','INT') AS AttributeCount
FROM @x.nodes('/*/*') T(C)
/*
AttributeCount
--------------
3
4
*/
--24.返回给定位置的属性值或者名称
DECLARE @x XML
SELECT @x = '
<checkworks dept="IT">
  <checkwork NAME="menbers" SEX="M" QQ="5454545454"/>
  <checkwork NAME="FF013" SEX="F" QQ="5345454554" TEL="18800009999"/>
</checkworks>'
--返回第一个checkwork节点的第一个位置的属性值
SELECT  @x.value('(/checkworks/checkwork[1]/@*[position()=1])[1]','VARCHAR(20)') AS AttValue
/*
AttValue
--------------------
menbers
*/
--返回第二个checkwork节点的第四个位置的属性值
SELECT  @x.value('(/checkworks/checkwork[2]/@*[position()=4])[1]','VARCHAR(20)') AS AttValue
/*
AttValue
--------------------
18800009999
*/
--返回第一个元素的第三个属性值
SELECT  @x.value('local-name((/checkworks/checkwork[1]/@*[position()=3])[1])','VARCHAR(20)') AS AttName
/*
AttName
--------------------
QQ
*/
--返回第二个元素的第四个属性值
SELECT  @x.value('local-name((/checkworks/checkwork[2]/@*[position()=4])[1])','VARCHAR(20)') AS AttName
/*
AttName
--------------------
TEL
*/
--通过变量传递位置返回属性值
DECLARE @Elepos INT,@Attpos INT
SELECT @Elepos=2,@Attpos = 3
SELECT  @x.value('local-name((/checkworks/checkwork[sql:variable("@Elepos")]/@*[position()=sql:variable("@Attpos")])[1])','VARCHAR(20)') AS AttName
/*
AttName
--------------------
QQ
*/
--25.判断是XML中否存在相应的属性
DECLARE  @x XML
SELECT @x = '<checkwork NAME="FF013" SEX="F" QQ="5345454554" TEL="18800009999"/>'
IF @x.exist('/checkwork/@NAME') = 1
  SELECT 'Exists' AS Result
ELSE
  SELECT 'Does not exist' AS Result
/*
Result
------
Exists
*/
--传递变量判断是否存在
DECLARE  @x XML
SELECT @x = '<checkwork NAME="FF013" SEX="F" QQ="5345454554" TEL="18800009999"/>'
DECLARE @att VARCHAR(20)
SELECT @att = 'QQ'
IF @x.exist('/checkwork/@*[local-name()=sql:variable("@att")]') = 1
  SELECT 'Exists' AS Result
ELSE
  SELECT 'Does not exist' AS Result
/*
Result
------
Exists
*/
--26.循环遍历元素的所有属性
DECLARE  @x XML
SELECT @x = '<checkwork NAME="FF013" SEX="F" QQ="5345454554" TEL="18800009999"/>'
DECLARE
  @cnt INT,
  @totCnt INT,
  @attName VARCHAR(30),
  @attValue VARCHAR(30)
SELECT
  @cnt = 1,
  @totCnt = @x.value('count(/checkwork/@*)','INT')--获得属性总数量
-- loop
WHILE @cnt <= @totCnt BEGIN
  SELECT
    @attName = @x.value(
      'local-name((/checkwork/@*[position()=sql:variable("@cnt")])[1])',
      'VARCHAR(30)'),
    @attValue = @x.value(
      '(/checkwork/@*[position()=sql:variable("@cnt")])[1]',
      'VARCHAR(30)')
  PRINT 'Attribute Position: ' + CAST(@cnt AS VARCHAR)
  PRINT 'Attribute Name: ' + @attName
  PRINT 'Attribute Value: ' + @attValue
  PRINT ''
  -- increment the counter variable
  SELECT @cnt = @cnt + 1
END
/*
Attribute Position: 1
Attribute Name: NAME
Attribute Value: FF013
Attribute Position: 2
Attribute Name: SEX
Attribute Value: F
Attribute Position: 3
Attribute Name: QQ
Attribute Value: 5345454554
Attribute Position: 4
Attribute Name: TEL
Attribute Value: 18800009999
*/
--27.返回指定位置的子元素
DECLARE @x XML
SELECT @x = '
<checkworks dept="IT">
  <checkwork NAME="menbers" SEX="M" QQ="5454545454"/>
  <checkwork NAME="FF013" SEX="F" QQ="5345454554" TEL="18800009999"/>
</checkworks>'
SELECT @x.query('(/checkworks/checkwork)[1]')
/*
<checkwork NAME="menbers" SEX="M" QQ="5454545454" />
*/
SELECT @x.query('(/checkworks/checkwork)[position()=2]')
/*
<checkwork NAME="FF013" SEX="F" QQ="5345454554" TEL="18800009999" />
*/
--通过变量获取指定位置的子元素
DECLARE @i INT
SELECT @i = 2
SELECT @x.query('(/checkworks/checkwork)[sql:variable("@i")]')
--or
SELECT @x.query('(/checkworks/checkwork)[position()=sql:variable("@i")]')
/*
<checkwork NAME="FF013" SEX="F" QQ="5345454554" TEL="18800009999" />
*/
--28.循环遍历获得所有子元素
DECLARE @x XML
SELECT @x = '
<checkworks dept="IT">
  <checkwork NAME="menbers" SEX="M" QQ="5454545454"/>
  <checkwork NAME="FF013" SEX="F" QQ="5345454554" TEL="18800009999"/>
</checkworks>'
DECLARE
  @cnt INT,
  @totCnt INT,
  @child XML
-- counter variables
SELECT
  @cnt = 1,
  @totCnt = @x.value('count(/checkworks/checkwork)','INT')
-- loop
WHILE @cnt <= @totCnt BEGIN
  SELECT
    @child = @x.query('/checkworks/checkwork[position()=sql:variable("@cnt")]')
  PRINT 'Processing Child Element: ' + CAST(@cnt AS VARCHAR)
  PRINT 'Child element: ' + CAST(@child AS VARCHAR(100))
  PRINT ''
  -- incremet the counter variable
  SELECT @cnt = @cnt + 1
END
/*
Processing Child Element: 1
Child element: <checkwork NAME="menbers" SEX="M" QQ="5454545454"/>
Processing Child Element: 2
Child element: <checkwork NAME="FF013" SEX="F" QQ="5345454554" TEL="18800009999"/>
ac.char
关注
  • 0
    点赞
  • 踩
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值