使用SQL操作XML

DECLARE @xmlData XML

SET @xmlData = '<SheetData>
     <row ForecastType="Overwrite" Jan="1" Feb="2" Mar="3" Apr="4" May="5" />
     <row ForecastType="Overwrite" Jan="10" Feb="20" Mar="30" Apr="40" May="50" />
    </SheetData>'

 

select @xmlData

 

DECLARE @tmpDT TABLE(ForecastType NVARCHAR(50),
      Jan NVARCHAR(50),
      Feb NVARCHAR(50),
      Mar NVARCHAR(50),
      Apr NVARCHAR(50),
      May NVARCHAR(50))

INSERT @tmpDT
SELECT T.c.value('@ForecastType', 'NVARCHAR(50)') as ForecastType,
       T.c.value('@Jan', 'NVARCHAR(50)') as Jan,
       T.c.value('@Feb', 'NVARCHAR(50)') as Feb,
       T.c.value('@Mar', 'NVARCHAR(50)') as Mar,
       T.c.value('@Apr', 'NVARCHAR(50)') as Apr,
       T.c.value('@May', 'NVARCHAR(50)') as May
FROM @xmlData.nodes('/SheetData/row') T(c)

 

SELECT * FROM @tmpDT

 

SET @xmlData = (SELECT ForecastType, Jan as January, Feb, Mar, Apr, May FROM @tmpDT as row FOR XML AUTO, ROOT('SheetData'))

 

SELECT @xmlData