sql - 将 XML 分解为 SQL 表时遇到问题

Question

使用 SQL Server 2008，我需要将此 XML 选择到表中。我无法让它返回每一行。

Declare @CurrentStatusInfoList xml
Set @CurrentStatusInfoList = '<CurrentStatusInfoList>
  <CurrentStatusInfo Caption="Route" ItemID="568" eFolderID="0900000000000000000000000058170" eStageName="ustg_Route" /> 
  <CurrentStatusInfo Caption="Route" ItemID="615" eFolderID="0900000000000000000000000059580" eStageName="ustg_Route" /> 
  <CurrentStatusInfo Caption="Route" ItemID="1199" eFolderID="0900000000000000000000000066117" eStageName="ustg_Route" /> 
  <CurrentStatusInfo Caption="Route" ItemID="997" eFolderID="0900000000000000000000000063447" eStageName="ustg_Route" /> 
  <CurrentStatusInfo Caption="Route" ItemID="1208" eFolderID="0900000000000000000000000066265" eStageName="ustg_Route" /> 
  <CurrentStatusInfo Caption="Route" ItemID="1577" eFolderID="0900000000000000000000000069853" eStageName="ustg_Route" /> 
</CurrentStatusInfoList>'

 DECLARE @StatusTable 
    TABLE(           
             eFolderID varchar(100),
             ItemID bigint,
             eStageName varchar(100),
             Caption varchar(100)
         )

 INSERT INTO              
  @StatusTable (eFolderID, ItemID,eStageName,Caption)              
 SELECT               
  @CurrentStatusInfoList.value('(//@eFolderID)[1]', 'varchar(100)') as eFolderID,
  @CurrentStatusInfoList.value('(//@ItemID)[1]', 'bigint') as ItemID,
  @CurrentStatusInfoList.value('(//@eStageName)[1]', 'varchar(100)') as eStageName, 
  @CurrentStatusInfoList.value('(//@Caption)[1]', 'varchar(100)') as Caption 
FROM @CurrentStatusInfoList.nodes('//CurrentStatusInfoList/CurrentStatusInfo') 
    AS ParamValues(eFolderID)                 

 Select * from @StatusTable 

Answer 1

尝试这个:

Declare @CurrentStatusInfoList xml
Set @CurrentStatusInfoList = '<CurrentStatusInfoList>
  <CurrentStatusInfo Caption="Route" ItemID="568" eFolderID="0900000000000000000000000058170" eStageName="ustg_Route" /> 
  <CurrentStatusInfo Caption="Route" ItemID="615" eFolderID="0900000000000000000000000059580" eStageName="ustg_Route" /> 
  <CurrentStatusInfo Caption="Route" ItemID="1199" eFolderID="0900000000000000000000000066117" eStageName="ustg_Route" /> 
  <CurrentStatusInfo Caption="Route" ItemID="997" eFolderID="0900000000000000000000000063447" eStageName="ustg_Route" /> 
  <CurrentStatusInfo Caption="Route" ItemID="1208" eFolderID="0900000000000000000000000066265" eStageName="ustg_Route" /> 
  <CurrentStatusInfo Caption="Route" ItemID="1577" eFolderID="0900000000000000000000000069853" eStageName="ustg_Route" /> 
</CurrentStatusInfoList>'

 DECLARE @StatusTable 
    TABLE(           
             eFolderID varchar(100),
             ItemID bigint,
             eStageName varchar(100),
             Caption varchar(100)
         )

 INSERT INTO              
  @StatusTable (eFolderID, ItemID,eStageName,Caption)              
 SELECT               
  CurrentStatusInfo.value('@eFolderID', 'varchar(100)') as eFolderID,
  CurrentStatusInfo.value('@ItemID', 'bigint') as ItemID,
  CurrentStatusInfo.value('@eStageName', 'varchar(100)') as eStageName, 
  CurrentStatusInfo.value('@Caption', 'varchar(100)') as Caption 
FROM @CurrentStatusInfoList.nodes('//CurrentStatusInfoList/CurrentStatusInfo') as CurrentStatusInfoList(CurrentStatusInfo)

 Select * from @StatusTable