[sql2005]查询包含xml类型的表

Question

有如下建表和插入数据的sqlCREATE TABLE StudentInfo
(
IdCard varchar(18) PRIMARY KEY,
StuName xml,
StuSex char(1) CHECK(StuSex IN ('0', '1'))
)
INSERT INTO StudentInfo
SELECT '130221199904244600', '<StuName><Name language="en-US">Jordan</Name><Name language="zh-CN">乔丹</Name><Name language="zh-TW">喬丹</Name></StuName>', '1'
UNION
SELECT '130221199904244601', '<StuName><Name language="en-US">Kobe</Name><Name language="zh-CN">科比</Name><Name language="zh-TW">科比</Name></StuName>', '1'
UNION
SELECT '130221199904244602', '<StuName><Name language="en-US">YaoMing</Name><Name language="zh-CN">姚明</Name><Name language="zh-TW">姚明</Name></StuName>', '1'
UNION
SELECT '130221199904244603', '<StuName><Name language="en-US">McGrady</Name><Name language="zh-CN">麦迪</Name><Name language="zh-TW">麥迪</Name></StuName>', '1'
第一个查询：查找StuName中Name值为 '乔丹'的记录
第二个查询：查找StuName字段中language="zh-CN"且Name值为'麦迪'的记录
注意：这两个查询要返回整条的记录，也就是要返回IdCard，StuName， StuSex 这三个字段的记录
第一个就别用like了，第二个也别charindex来charindex去的定位了。如果您实在不知道其它的办法，麻烦您就高抬贵手，留个脚印就行了。别弄一堆这样的代码。

Answer 1

再加个第三个查询吧：查找StuName字段中language="zh-CN"且Name值为'麦迪'且StuSex='1'的记录

Answer 2

SELECT * FROM StudentInfo 
WHERE StuName.exist('//Name[contains(.,"乔丹")]')=1;SELECT * FROM StudentInfo 
WHERE StuName.exist('//Name[@language="en_US" and contains(.,"麦迪")]')=1

Answer 3

SELECT * FROM StudentInfo 
WHERE StuName.exist('//Name[@language="zh-CN" and contains(.,"麦迪")]')=1
    AND StuSex=1;

Answer 4

CREATE TABLE StudentInfo
(
    IdCard varchar(18) PRIMARY KEY,
    StuName xml,
    StuSex char(1) CHECK(StuSex IN ('0', '1'))
)
INSERT INTO StudentInfo
SELECT '130221199904244600', '<StuName><Name language="en-US">Jordan</Name><Name language="zh-CN">乔丹</Name><Name language="zh-TW">喬丹</Name></StuName>', '1'
UNION
SELECT '130221199904244601', '<StuName><Name language="en-US">Kobe</Name><Name language="zh-CN">科比</Name><Name language="zh-TW">科比</Name></StuName>', '1'
UNION
SELECT '130221199904244602', '<StuName><Name language="en-US">YaoMing</Name><Name language="zh-CN">姚明</Name><Name language="zh-TW">姚明</Name></StuName>', '1'
UNION
SELECT '130221199904244603', '<StuName><Name language="en-US">McGrady</Name><Name language="zh-CN">麦迪</Name><Name language="zh-TW">麥迪</Name></StuName>', '1'select * 
from StudentInfo
where CHARINDEX('乔丹',cast(StuName as  varchar(2000)))>0
/*
IdCard             StuName                                                                                                                                                                                                                                                          StuSex
------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------
130221199904244600 <StuName><Name language="en-US">Jordan</Name><Name language="zh-CN">乔丹</Name><Name language="zh-TW">喬丹</Name></StuName>                                                                                                                                          1*/

Answer 5

where stuname.exist('/StuName/Name[@language ="zh-CN" and contains(.,"乔丹")]')=1

Answer 6

这个……除了like exists charindex之外，没有专门用于xml作为条件的查询方法么？

Answer 7

CREATE TABLE StudentInfo
(
    IdCard varchar(18) PRIMARY KEY,
    StuName xml,
    StuSex char(1) CHECK(StuSex IN ('0', '1'))
)
INSERT INTO StudentInfo
SELECT '130221199904244600', '<StuName><Name language="en-US">Jordan</Name><Name language="zh-CN">乔丹</Name><Name language="zh-TW">喬丹</Name></StuName>', '1'
UNION
SELECT '130221199904244601', '<StuName><Name language="en-US">Kobe</Name><Name language="zh-CN">科比</Name><Name language="zh-TW">科比</Name></StuName>', '1'
UNION
SELECT '130221199904244602', '<StuName><Name language="en-US">YaoMing</Name><Name language="zh-CN">姚明</Name><Name language="zh-TW">姚明</Name></StuName>', '1'
UNION
SELECT '130221199904244603', '<StuName><Name language="en-US">McGrady</Name><Name language="zh-CN">麦迪</Name><Name language="zh-TW">麥迪</Name></StuName>', '1'SELECT * FROM StudentInfo 
WHERE StuName.exist('//Name[contains(.,"乔丹")]')=1;SELECT * FROM StudentInfo 
WHERE StuName.exist('//Name[@language="en_US" and contains(.,"麦迪")]')=1SELECT * FROM StudentInfo 
WHERE StuName.exist('//Name[@language="zh-CN" and contains(.,"麦迪")]')=1
    AND StuSex=1;DROP TABLE StudentInfo/*
IdCard             StuName                                                                                                                                                                                                                                                          StuSex
------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------
130221199904244600 <StuName><Name language="en-US">Jordan</Name><Name language="zh-CN">乔丹</Name><Name language="zh-TW">喬丹</Name></StuName>                                                                                                                                          1(1 行受影响)IdCard             StuName                                                                                                                                                                                                                                                          StuSex
------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------(0 行受影响)IdCard             StuName                                                                                                                                                                                                                                                          StuSex
------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------
130221199904244603 <StuName><Name language="en-US">McGrady</Name><Name language="zh-CN">麦迪</Name><Name language="zh-TW">麥迪</Name></StuName>                                                                                                                                         1(1 行受影响)
*/