[sql server] 2005的xml查询例子

一、

--建立测试环境
IF OBJECT_ID('PRPXMAINCREDITINFO') IS NOT NULL  DROP TABLE PRPXMAINCREDITINFO
GO
CREATE TABLE PRPXMAINCREDITINFO
(
 SUBJECT varchar(10),
 chinesesubject varchar(20)
)
GO
INSERT PRPXMAINCREDITINFO
SELECT '公司1','样本公司1' union all
SELECT '公司2','样本公司2'
go
IF OBJECT_ID('PRPXTRADE') IS NOT NULL  DROP TABLE PRPXTRADE
GO
CREATE TABLE PRPXTRADE
(
 TRADECODE varchar(10),
 TRADENAME varchar(20)
)
GO
INSERT PRPXTRADE
SELECT '代号1','代号名字1'
--查询
select
(select SUBJECT as "SUBJECT",
 chinesesubject as "chinesesubject"
 from PRPXMAINCREDITINFO for xml path(''),type
) as "PRPXMAINCREDITINFO",
(select TRADECODE as "TRADECODE",
 TRADENAME as "TRADENAME" 
 from PRPXTRADE for xml path(''),type
) as "PRPXTRADE"
FOR XML PATH(''),type

--结果
/*
<PRPXMAINCREDITINFO>
<SUBJECT>公司1</SUBJECT><chinesesubject>样本公司1</chinesesubject>
<SUBJECT>公司2</SUBJECT><chinesesubject>样本公司2</chinesesubject>
</PRPXMAINCREDITINFO>
<PRPXTRADE>
<TRADECODE>代号1</TRADECODE>
<TRADENAME>代号名字1</TRADENAME>
</PRPXTRADE>

*/

 

二、

if OBJECT_ID('tempdb..#a') is not null
 drop table #a;
go
create table #a (id int,Employee xml);
go
insert into #a
 select 1,'<NewDataSet>
  <xs:schema xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" id="NewDataSet">
    <xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:MainDataTable="Table" msdata:UseCurrentLocale="true">
      <xs:complexType>
        <xs:choice minOccurs="0" maxOccurs="unbounded">
          <xs:element name="Table">
            <xs:complexType>
              <xs:sequence>
                <xs:element name="ZoneNo" type="xs:string" minOccurs="0" />
                <xs:element name="UserPosition" type="xs:string" minOccurs="0" />
                <xs:element name="PositionLevel" type="xs:string" minOccurs="0" />
                <xs:element name="RoleType" type="xs:string" minOccurs="0" />
                <xs:element name="WorkState" type="xs:string" minOccurs="0" />
                <xs:element name="RedeployTime" type="xs:dateTime" minOccurs="0" />
              </xs:sequence>
            </xs:complexType>
          </xs:element>
        </xs:choice>
      </xs:complexType>
    </xs:element>
  </xs:schema>
  <Table>
    <ID>1</ID>
    <ZoneNo>ZY0304</ZoneNo>
    <UserPosition>置业顾问</UserPosition>
    <PositionLevel>初级顾问</PositionLevel>
    <RoleType>0009</RoleType>
    <WorkState>在职</WorkState>
    <RedeployTime>2007-07-20 9:26:00</RedeployTime>
  </Table>
  <Table>
    <ID>2</ID>
    <ZoneNo>ZY0302</ZoneNo>
    <UserPosition>置业顾问</UserPosition>
    <PositionLevel>初级顾问</PositionLevel>
    <RoleType>0010</RoleType>
    <WorkState>在职</WorkState>
    <RedeployTime>2008-09-20 9:26:00</RedeployTime>
  </Table>
</NewDataSet>'
go

declare @s varchar(max)
select @s=isnull(@s+';'+char(10),'')+'update Table2 set ZoneNo='''+ZoneNo+''',UserPosition='''+UserPosition+''',PositionLevel='''+PositionLevel+
''',RoleType='''+RoleType+''' where RedeployTime>'''+RedeployTime+''''
from #a a cross apply
(select t.x.value('ID[1]','int') bid ,
 t.x.value('ZoneNo[1]','varchar(10)') ZoneNo,
 t.x.value('UserPosition[1]','varchar(10)') UserPosition,
 t.x.value('PositionLevel[1]','varchar(10)') PositionLevel,
 t.x.value('RoleType[1]','varchar(10)') RoleType,
 t.x.value('WorkState[1]','varchar(10)') WorkState,
t.x.value('RedeployTime[1]','varchar(30)') RedeployTime
from a.Employee.nodes('/NewDataSet/Table') t(x)) b

print @s

------------
update Table2 set ZoneNo='ZY0304',UserPosition='置业顾问',PositionLevel='初级顾问',RoleType='0009' where RedeployTime>'2007-07-20 9:26:00';
update Table2 set ZoneNo='ZY0302',UserPosition='置业顾问',PositionLevel='初级顾问',RoleType='0010' where RedeployTime>'2008-09-20 9:26:00'