问个查询问题

Question

数据库一个字段类型为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]

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

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

Answer 5

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 yORDER BY y.RowSELECT @Result

Answer 6

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 yORDER BY y.TimeSELECT @Result
根据时间排序