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
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
解决方案 »
- ms-sqlserver 自增字段 最大值问题
- sqlserver的存储过程可以在mysql5.0版本里面直接执行吗
- replace的使用,在线等,马上给分啊
- 表中的截取查询,请高人指教!
- SQL SERVER存储过程里取到一个list,如何在存储过程里再循环读取
- 数据库备份的完整备份、差异备份、日志备份的疑问,求解
- 一个非常难找出原因的包适sql server2000的问题,但我不懂放在这来提适不适合,但如解决,高分相送
- 送分了!请教一个DataTime或者smallDataTime在日期查询中的问题
- 请帮忙解释一下,菜鸟入门
- 究竟用不用数据库(郁闷中)大家讨论,论据精辟者有加分
- 急,MS-SQL SERVER远程telnet ip 1433连接失败
- 一个表自关联查询问题
http://topic.csdn.net/u/20081107/17/68aaf5a9-c596-4ab5-ae18-f3370b2ab35b.html
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 行受影响)
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 行受影响)
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
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 行受影响)