问个查询问题

[haonanernet]

数据库一个字段类型为ntext,存取内容如下：

<NewDataSet> <xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"> <xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:UseCurrentLocale="true"> <xs:complexType> <xs:choice minOccurs="0" maxOccurs="unbounded"> <xs:element name="Table1"> <xs:complexType> <xs:sequence> <xs:element name="User" type="xs:string" minOccurs="0" /> <xs:element name="UserName" type="xs:string" minOccurs="0" /> <xs:element name="Time" type="xs:string" minOccurs="0" /> <xs:element name="Text" type="xs:string" minOccurs="0" /> <xs:element name="Step" type="xs:string" minOccurs="0" /> <xs:element name="Form" type="xs:string" minOccurs="0" /> <xs:element name="Signature" type="xs:string" minOccurs="0" /> </xs:sequence> </xs:complexType> </xs:element> </xs:choice> </xs:complexType> </xs:element> </xs:schema>
<Table1>
<User>caoxx</User>
<UserName>曹xx</UserName>
<Time>2011/10/31 15:34:29</Time>
<Text>请张xx阅处</Text>
<Step>0003</Step>
<Form>0005</Form>
</Table1>
<Table1>
<User>zhangxx</User>
<UserName>张xx</UserName>
<Time>2011/11/02 19:37:16</Time>
<Text>请计划部负责落实。</Text>
<Step>0003</Step>
<Form>0005</Form>
</Table1>
</NewDataSet>

如何通过查询把这个字段的所有行的内容全部转化成

请张xx阅处[曹xx]，请计划部负责落实[张xx]

[nvshenfann]

这是导出还是什么？

[haonanernet]

是查询

[开着拖拉机泡妞]

这么多，能不能精简点，看得头都晕了

[苦苦的潜行者]

正则
(?i)(((?<=<username>).*(?=</username>))|((?<=<text>).*(?=</text>)))
取分组1，结果为
------
曹xx
请张xx阅处
张xx
请计划部负责落实。

[dawugui]

如果是能确定这样:<UserName>曹xx</UserName>还有点办法.帮顶.

[-晴天]

create table tb(txt text)
insert into tb select '<NewDataSet> <xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"> <xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:UseCurrentLocale="true"> <xs:complexType> <xs:choice minOccurs="0" maxOccurs="unbounded"> <xs:element name="Table1"> <xs:complexType> <xs:sequence> <xs:element name="User" type="xs:string" minOccurs="0" /> <xs:element name="UserName" type="xs:string" minOccurs="0" /> <xs:element name="Time" type="xs:string" minOccurs="0" /> <xs:element name="Text" type="xs:string" minOccurs="0" /> <xs:element name="Step" type="xs:string" minOccurs="0" /> <xs:element name="Form" type="xs:string" minOccurs="0" /> <xs:element name="Signature" type="xs:string" minOccurs="0" /> </xs:sequence> </xs:complexType> </xs:element> </xs:choice> </xs:complexType> </xs:element> </xs:schema>   
<Table1>   
<User>caoxx</User>   
<UserName>曹xx</UserName>   
<Time>2011/10/31 15:34:29</Time>   
<Text>请张xx阅处</Text>  
<Step>0003</Step>   
<Form>0005</Form>   
</Table1>   
<Table1>   
<User>zhangxx</User>   
<UserName>张xx</UserName>   
<Time>2011/11/02 19:37:16</Time>   
<Text>请计划部负责落实。</Text>   
<Step>0003</Step>   
<Form>0005</Form>   
</Table1>   
</NewDataSet>'
go
declare @xml xml
select @xml=txt from tb
select 
t.c.value('UserName[1]','varchar(20)') UserName,
t.c.value('Text[1]','varchar(20)') Txt
from @xml.nodes('//Table1') t(c);
/*
UserName             Txt
-------------------- --------------------
曹xx                  请张xx阅处
张xx                  请计划部负责落实。

(2 行受影响)

*/
go
drop table tb

[SQL77]

[Quote=引用 6 楼 qianjin036a 的回复:]

SQL code
create table tb(txt text)
insert into tb select '<NewDataSet> <xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msd……
[/Quote]
UP ,XML处理

[haonanernet]

能否把结果变成这样呢: 为一行，中间用分号隔开
Desc
---------------------------------------
曹xx:请张xx阅处;张xx:请计划部负责落实。




[Quote=引用 6 楼 qianjin036a 的回复:]
SQL code

create table tb(txt text)
insert into tb select '<NewDataSet> <xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-m……
[/Quote]

[我腫了]

IF OBJECT_ID('tempdb..#t1')IS NOT NULL	
	DROP TABLE #t1
GO
SELECT CONVERT(ntext,
N'<NewDataSet> <xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"> <xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:UseCurrentLocale="true"> <xs:complexType> <xs:choice minOccurs="0" maxOccurs="unbounded"> <xs:element name="Table1"> <xs:complexType> <xs:sequence> <xs:element name="User" type="xs:string" minOccurs="0" /> <xs:element name="UserName" type="xs:string" minOccurs="0" /> <xs:element name="Time" type="xs:string" minOccurs="0" /> <xs:element name="Text" type="xs:string" minOccurs="0" /> <xs:element name="Step" type="xs:string" minOccurs="0" /> <xs:element name="Form" type="xs:string" minOccurs="0" /> <xs:element name="Signature" type="xs:string" minOccurs="0" /> </xs:sequence> </xs:complexType> </xs:element> </xs:choice> </xs:complexType> </xs:element> </xs:schema>   
<Table1>   
<User>caoxx</User>   
<UserName>曹xx</UserName>   
<Time>2011/10/31 15:34:29</Time>   
<Text>请张xx阅处</Text>  
<Step>0003</Step>   
<Form>0005</Form>   
</Table1>   
<Table1>   
<User>zhangxx</User>   
<UserName>张xx</UserName>   
<Time>2011/11/02 19:37:16</Time>   
<Text>请计划部负责落实。</Text>   
<Step>0003</Step>   
<Form>0005</Form>   
</Table1>   
</NewDataSet>') AS Text INTO #t1


IF OBJECT_ID('tempdb..#tmp')IS NOT NULL	
	DROP TABLE #tmp
Go
Select Convert(xml,Text) AS Xml INTO #tmp FROM #t1


DECLARE @Result NVARCHAR(MAX)

SELECT @Result=isnull(@Result+',','')+y.Text+QUOTENAME(y.UserName) FROM #tmp AS x
	CROSS APPLY((SELECT ROW_NUMBER()OVER(ORDER BY GETDATE()) as Row
						--,o.x.value('./User[1]','nvarchar(50)') AS[ User]
						,o.x.value('./UserName[1]','nvarchar(50)') AS UserName
						--,o.x.value('./Time[1]','nvarchar(50)') AS Time
						,o.x.value('./Text[1]','nvarchar(50)') AS Text
						--,o.x.value('./Step[1]','nvarchar(50)') AS Step
						--,o.x.value('./Form[1]','nvarchar(50)') AS Form
					FROM x.Xml.nodes('NewDataSet/Table1') AS o(x))
			) AS y

ORDER BY y.Row

SELECT @Result

[我腫了]

IF OBJECT_ID('tempdb..#t1')IS NOT NULL	
	DROP TABLE #t1
GO
SELECT CONVERT(ntext,
N'<NewDataSet> <xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"> <xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:UseCurrentLocale="true"> <xs:complexType> <xs:choice minOccurs="0" maxOccurs="unbounded"> <xs:element name="Table1"> <xs:complexType> <xs:sequence> <xs:element name="User" type="xs:string" minOccurs="0" /> <xs:element name="UserName" type="xs:string" minOccurs="0" /> <xs:element name="Time" type="xs:string" minOccurs="0" /> <xs:element name="Text" type="xs:string" minOccurs="0" /> <xs:element name="Step" type="xs:string" minOccurs="0" /> <xs:element name="Form" type="xs:string" minOccurs="0" /> <xs:element name="Signature" type="xs:string" minOccurs="0" /> </xs:sequence> </xs:complexType> </xs:element> </xs:choice> </xs:complexType> </xs:element> </xs:schema>   
<Table1>   
<User>caoxx</User>   
<UserName>曹xx</UserName>   
<Time>2011/10/31 15:34:29</Time>   
<Text>请张xx阅处</Text>  
<Step>0003</Step>   
<Form>0005</Form>   
</Table1>   
<Table1>   
<User>zhangxx</User>   
<UserName>张xx</UserName>   
<Time>2011/11/02 19:37:16</Time>   
<Text>请计划部负责落实。</Text>   
<Step>0003</Step>   
<Form>0005</Form>   
</Table1>   
</NewDataSet>') AS Text INTO #t1


IF OBJECT_ID('tempdb..#tmp')IS NOT NULL	
	DROP TABLE #tmp
Go
Select Convert(xml,Text) AS Xml INTO #tmp FROM #t1


DECLARE @Result NVARCHAR(MAX)

SELECT @Result=isnull(@Result+',','')+y.Text+QUOTENAME(y.UserName) FROM #tmp AS x
	CROSS APPLY((SELECT 
						o.x.value('./UserName[1]','nvarchar(50)') AS UserName
						--,o.x.value('./User[1]','nvarchar(50)') AS[User]
						,o.x.value('./Time[1]','nvarchar(50)') AS Time
						,o.x.value('./Text[1]','nvarchar(50)') AS Text
						--,o.x.value('./Step[1]','nvarchar(50)') AS Step
						--,o.x.value('./Form[1]','nvarchar(50)') AS Form
					FROM x.Xml.nodes('NewDataSet/Table1') AS o(x))
			) AS y

ORDER BY y.Time

SELECT @Result

根据时间排序

[AcHerat]

create table tb(txt text)
insert into tb select '<NewDataSet> <xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"> <xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:UseCurrentLocale="true"> <xs:complexType> <xs:choice minOccurs="0" maxOccurs="unbounded"> <xs:element name="Table1"> <xs:complexType> <xs:sequence> <xs:element name="User" type="xs:string" minOccurs="0" /> <xs:element name="UserName" type="xs:string" minOccurs="0" /> <xs:element name="Time" type="xs:string" minOccurs="0" /> <xs:element name="Text" type="xs:string" minOccurs="0" /> <xs:element name="Step" type="xs:string" minOccurs="0" /> <xs:element name="Form" type="xs:string" minOccurs="0" /> <xs:element name="Signature" type="xs:string" minOccurs="0" /> </xs:sequence> </xs:complexType> </xs:element> </xs:choice> </xs:complexType> </xs:element> </xs:schema>   
<Table1>   
<User>caoxx</User>   
<UserName>曹xx</UserName>   
<Time>2011/10/31 15:34:29</Time>   
<Text>请张xx阅处</Text>  
<Step>0003</Step>   
<Form>0005</Form>   
</Table1>   
<Table1>   
<User>zhangxx</User>   
<UserName>张xx</UserName>   
<Time>2011/11/02 19:37:16</Time>   
<Text>请计划部负责落实。</Text>   
<Step>0003</Step>   
<Form>0005</Form>   
</Table1>   
</NewDataSet>'
go
declare @xml xml
select @xml=txt from tb
;with ach as
(
select 
t.c.value('UserName[1]','varchar(20)') UserName,
t.c.value('Text[1]','varchar(20)') Txt
from @xml.nodes('//Table1') t(c)
)

select stuff((select ';'+UserName+' '+Txt from ach for xml path('')),1,1,'')
/*
-----------------------------------------
曹xx 请张xx阅处;张xx 请计划部负责落实。

(1 行受影响)

*/
go
drop table tb