新增文章

文章标题分类内容

SQL Server解析XML数据的方法详解之一

1、下面为多种方法从XML中读取EMAIL

DECLARE @x XMLSELECT @x = '<People>
  <dongsheng>
    <Info Name="Email">dongsheng@xxyy.com</Info>
    <Info Name="Phone">678945546</Info>
    <Info Name="qq">36575</Info>
  </dongsheng>
</People>'
-- 方法1
SELECT @x.value('data(/People/dongsheng/Info[@Name="Email"])[1]', 'varchar(30)')
-- 方法2
SELECT @x.value('(/People/dongsheng/Info[@Name="Email"])[1]', 'varchar(30)')
-- 方法3
SELECT C.value('.','varchar(30)') FROM @x.nodes('/People/dongsheng/Info[@Name="Email"]') T(C)
-- 方法4
SELECT C.value('(Info[@Name="Email"])[1]','varchar(30)') FROM @x.nodes('/People/dongsheng') T(C)
-- 方法5
SELECT C.value('(dongsheng/Info[@Name="Email"])[1]','varchar(30)') FROM @x.nodes('/People') T(C)
-- 方法6
SELECT C.value('.','varchar(30)') FROM @x.nodes('/People/dongsheng/Info') T(C) 
WHERE C.value('(.[@Name="Email"])[1]','varchar(30)') IS NOT NULL
-- 方法7
SELECT C.value('.','varchar(30)') FROM @x.nodes('/People/dongsheng/Info') T(C)WHERE C.exist('(.[@Name="Email"])[1]') = 1

2、读取XML中的属性值

DECLARE @x XML
SELECT @x =
'<Peoples>
  <People Name="tudou" Sex="女" />
  <People Name="choushuigou" Sex="女"/>
  <People Name="dongsheng" Sex="男" />
</Peoples>'
SELECT v.value('@Name[1]','VARCHAR(20)') AS Name, v.value('@Sex[1]','VARCHAR(20)') AS Sex
FROM @x.nodes('/Peoples/People') x(v)

3、属性过滤

DECLARE @x XML
SELECT @x = '
<Employees>
 <Employee id="1234" dept="IT" type="合同工">
  <Info NAME="dongsheng" SEX="男" QQ="5454545454"/>
 </Employee>
 <Employee id="5656" dept="IT" type="临时工">
  <Info NAME="土豆" SEX="女" QQ="5345454554"/>
 </Employee>
 <Employee id="3242" dept="市场" type="合同工">
  <Info NAME="choushuigou" SEX="女" QQ="54543545"/>
 </Employee>
</Employees>'
--查询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('/Employees/Employee[@dept="IT"]/Info') T(C)
  --方法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('//Employee[@dept="IT"]/*') T(C)
--查询出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('//Employee[@dept="IT"][@type="合同工"]/*') T(C)