SQL 中操作 XML

 ----------------------------------------------------------------------------------------------------------------------------------------------
 ----------------------------------------------------------------------------------------------------------------------------------------------
DECLARE @idoc int
DECLARE @doc varchar(1000)
SET @doc ='
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
   <Order CustomerID="VINET" EmployeeID="5" OrderDate="1996-07-04T00:00:00">
      <OrderDetail OrderID="10248" ProductID="11" Quantity="12"/>
      <OrderDetail OrderID="10248" ProductID="42" Quantity="10"/>
   </Order>
</Customer>
</ROOT>'

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

SELECT    *
FROM       OPENXML (@idoc, '/ROOT/Customer',3)
            WITH (CustomerID  varchar(10),
                  ContactName varchar(20))
WHERE .........

 ----------------------------------------------------------------------------------------------------------------------------------------------
 ----------------------------------------------------------------------------------------------------------------------------------------------

DECLARE @data XML
SET @data='<Root><line id="1" name="string"/><line id="2" name="string2"/><datetime>2009/09/21</datetime></Root>'
SELECT @data.value('(/Root ne/@id)[1]','int')
SELECT @data.value('(/Root ne/@name)[1]','nvarchar(50)')
SELECT @data.value('(/Root/datetime)[1]','datetime')
SELECT @data.value('(/Root ne/@id)[2]','int')
Select T.c.value('@id','int') as [id],T.c.value('@name','Nvarchar(50)') as [name]
From @data.nodes('/Root ne') T(c) 

 ----------------------------------------------------------------------------------------------------------------------------------------------
 ----------------------------------------------------------------------------------------------------------------------------------------------

DECLARE @LayoutTable TABLE(IsShow bit)
DECLARE @OutputTable TABLE(ID INT, [Name] NVARCHAR(50), Age INT, Gender CHAR(1), WorkExpYear INT, LastModifiedDate DATETIME)
DECLARE @DataTable TABLE(Recount INT, PageCount INT, NavLabel NVARCHAR(50))
DECLARE @DisplayFormatTags TABLE([name] NVARCHAR(50), width NVARCHAR(8), [key] NVARCHAR(50),FieldType INT, SortID INT )

INSERT INTO @LayoutTable (IsShow) VALUES (1)
INSERT INTO @OutputTable (ID, [Name], Age, Gender, WorkExpYear, LastModifiedDate) VALUES (1, N'Test1', 1, 'W', 1, GETDATE() )
INSERT INTO @OutputTable (ID, [Name], Age, Gender, WorkExpYear, LastModifiedDate) VALUES (2, N'Test2', 2, 'M', 2, GETDATE() ) 
INSERT INTO @DataTable (Recount, PageCount, NavLabel) VALUES (10, 1, '' )
INSERT INTO @DisplayFormatTags ([name], width, [key], FieldType, SortID ) VALUES ('Name', '120', 'Name', 1, 1 )
INSERT INTO @DisplayFormatTags ([name], width, [key], FieldType, SortID ) VALUES ('Age', '50', 'Age', 1, 2 )
INSERT INTO @DisplayFormatTags ([name], width, [key], FieldType, SortID ) VALUES ('Gender', '50', 'Gender', 1, 4 )
INSERT INTO @DisplayFormatTags ([name], width, [key], FieldType, SortID ) VALUES ('Year of Experience', '50', 'WorkExpYear', 1, 3 )
INSERT INTO @DisplayFormatTags ([name], width, [key], FieldType, SortID ) VALUES ('Modified Date', '120', 'LastModifiedDate', 1, 5 )

SELECT IsShow AS IsShow,
 (                                     
  select *
  FROM @OutputTable as [Line]
  WHERE Age > 1
  order by id
  for xml auto,type,root('Lines')
    ),
    (
  select Recount AS [Recount], PageCount AS [PageCount], NavLabel AS [NavLabel],
   (
    select  [name] AS [name], width AS [width], [key] AS [key],
     case FieldType                               
      when 1 then ''
      when 2 then 'DM_image'
      when 3 then 'DM_link'
      when 4 then 'DM_RedFont'
      when 5 then 'DM_noReplaceHTML'
      WHEN 6 THEN 'DM_MouseOver'
     end as colType
    FROM @DisplayFormatTags as [Display]
    --WHERE
    ORDER BY SortID
    for xml auto,type
   )
  FROM @DataTable as [DisplayFormat]
  for xml auto,type
    )
FROM @LayoutTable AS [ListApplication]
FOR XML AUTO,TYPE,ROOT('xml')

 ----------------------------------------------------------------------------------------------------------------------------------------------
 ----------------------------------------------------------------------------------------------------------------------------------------------

SELECT *
FROM InterviewMT_TimeSlot_Interviewee FOR XML Auto

SELECT AccountID '@AccountID', TimeSlotID 'TimeSlot', JobID 'Job/@JobID', CandidateID 'Job/Candidate/@CandidateID'
FROM InterviewMT_TimeSlot_Interviewee FOR XML RAW('TimeSlotID')

SELECT * FROM InterviewMT_TimeSlot_Interviewee FOR XML AUTO,ROOT('MyRoot')

SELECT AccountID '@AccountID', TimeSlotID 'TimeSlot', JobID 'Job/@JobID', CandidateID 'Job/Candidate/@CandidateID'
FROM InterviewMT_TimeSlot_Interviewee FOR XML PATH('TimeSlotID')