XML Query

Question

declare @str xml
set @str ='<root>
<row servername="CIPUSQLL26\SQL2005" dbname="Lab_SQL_Server_Inventory" cnt="8"/>
<row servername="CIPUSQLL26\SQL2005" dbname="Distribution" cnt="2"/>
<row servername="CIPUSQLL26\SQL2005" dbname="ReportServer2" cnt="3"/>
</root>'
select @str;我想得到如下结果并需要插入到一个表中(不能以XML形式返回).谢谢！servarname              dbname                         cnt
CIPUSQLL26\SQL2005      Lab_SQL_Server_Inventory        8
CIPUSQLL26\SQL2005      Distribution                    2
CIPUSQLL26\SQL2005      ReportServer2                   3

Answer 1

可以在前台程序,直接利用DATASET.READXML方法读出来

Answer 2

SQLServer2005 XML在T－SQL查询中的典型应用
http://topic.csdn.net/u/20081107/17/68aaf5a9-c596-4ab5-ae18-f3370b2ab35b.html

Answer 3

declare @str xml 
set @str =' <root> 
<row servername="CIPUSQLL26\SQL2005" dbname="Lab_SQL_Server_Inventory" cnt="8"/> 
<row servername="CIPUSQLL26\SQL2005" dbname="Distribution" cnt="2"/> 
<row servername="CIPUSQLL26\SQL2005" dbname="ReportServer2" cnt="3"/> 
</root>' select a.v.value('@servername','varchar(20)'),
a.v.value('@dbname','varchar(20)'),
a.v.value('@cnt','int')
from @str.nodes('/root/row') a(v)                                         
-------------------- -------------------- -----------
CIPUSQLL26\SQL2005   Lab_SQL_Server_Inven 8
CIPUSQLL26\SQL2005   Distribution         2
CIPUSQLL26\SQL2005   ReportServer2        3(3 行受影响)

Answer 4

declare @str xml 
set @str =' <root> 
<row servername="CIPUSQLL26\SQL2005" dbname="Lab_SQL_Server_Inventory" cnt="8"/> 
<row servername="CIPUSQLL26\SQL2005" dbname="Distribution" cnt="2"/> 
<row servername="CIPUSQLL26\SQL2005" dbname="ReportServer2" cnt="3"/> 
</root>' select [SERVERNAME]=a.v.value('@servername','varchar(20)'),
[DBNAME]=a.v.value('@dbname','varchar(20)'),
[CNT]=a.v.value('@cnt','int')
from @str.nodes('/root/row') a(v) SERVERNAME           DBNAME               CNT
-------------------- -------------------- -----------
CIPUSQLL26\SQL2005   Lab_SQL_Server_Inven 8
CIPUSQLL26\SQL2005   Distribution         2
CIPUSQLL26\SQL2005   ReportServer2        3(3 行受影响)

Answer 5

declare @str xml
set @str ='
<root>
<row servername="CIPUSQLL26\SQL2005" dbname="Lab_SQL_Server_Inventory" cnt="8"/>
<row servername="CIPUSQLL26\SQL2005" dbname="Distribution" cnt="2"/>
<row servername="CIPUSQLL26\SQL2005" dbname="ReportServer2" cnt="3"/>
</root>'
select 
t.x.value('./@servername[1]','varchar(50)') as name,
t.x.value('./@dbname[1]','varchar(50)') as name,
t.x.value('./@cnt[1]','varchar(50)') as name
from @str.nodes('//row') as t(x)name name name
CIPUSQLL26\SQL2005 Lab_SQL_Server_Inventory 8
CIPUSQLL26\SQL2005 Distribution 2
CIPUSQLL26\SQL2005 ReportServer2 3

Answer 6

declare @str xml
set @str =' <root> 
<row servername="CIPUSQLL26\SQL2005" dbname="Lab_SQL_Server_Inventory" cnt="8"/> 
<row servername="CIPUSQLL26\SQL2005" dbname="Distribution" cnt="2"/> 
<row servername="CIPUSQLL26\SQL2005" dbname="ReportServer2" cnt="3"/> 
</root>' select [SERVERNAME]=a.v.value('@servername','varchar(20)'),
[DBNAME]=a.v.value('@dbname','varchar(30)'),
[CNT]=a.v.value('@cnt','int')
from @str.nodes('/root/row') a(v)SERVERNAME           DBNAME                         CNT
-------------------- ------------------------------ -----------
CIPUSQLL26\SQL2005   Lab_SQL_Server_Inventory       8
CIPUSQLL26\SQL2005   Distribution                   2
CIPUSQLL26\SQL2005   ReportServer2                  3(3 行受影响)