SQL SERVER 解析XML到数据库表

//片段1
 IF OBJECT_ID('tempdb..#TEMP') IS NOT NULL
 BEGIN
     DROP TABLE  #TEMP
 END
 
 CREATE TABLE #TEMP
 ( 
      title VARCHAR(40)
     ,author VARCHAR(40)
     ,price VARCHAR(40) 
 )
 
 DECLARE @xmlDoc XML
 SET @xmlDoc='
 <books>
      <book id="0001">
           <title>C</title>
           <author>David</author>
           <price>21</price>
      </book>
      <book id="0002">
           <title>JAVA</title>
           <author>Jeriffe</author>
           <price>40</price>
      </book>
 </books>'
 
 SELECT @xmlDoc.query('(/*)')
 SELECT @xmlDoc.query('(/books)') 
 SELECT @xmlDoc.query('(/books/book)')
 
 SELECT @xmlDoc.value('(/books/book/author)[last()]','varchar(40)')  
 SELECT @xmlDoc.value('(/books/book[position()>1]/author)[1]','varchar(40)') 
  
 SELECT T.C.query('(author)') AS AuthorNode FROM @xmlDoc.nodes('/books/book') AS T ( C )
 
 SELECT T.C.value('(./author)[1]','varchar(40)') AS Author FROM @xmlDoc.nodes('/books/book') AS T ( C )
 SELECT T.C.value('(author)[1]','varchar(40)') AS Author FROM @xmlDoc.nodes('/books/book') AS T ( C )
 
 INSERT INTO #TEMP (title,author,price)
  SELECT 
      T.C.value('(./title)[1]', 'varchar(40)') 
     ,T.C.value('(./author)[1]', 'varchar(40)') 
     ,T.C.value('(./price)[1]', 'varchar(40)') 
   FROM @xmlDoc.nodes('/books/book') AS T ( C )

 SELECT * FROM #temp   

 //片段2

DECLARE@ImageNames XML
SET @ImageNames=
'<ImageNames>
    <ImageName>20-144-402-01.jpg</ImageName>
    <ImageName>20-144-402-02.jpg</ImageName>
    <ImageName>20-144-402-03.jpg</ImageName>
    <ImageName>20-144-402-04.jpg</ImageName>
</ImageNames>'

DECLARE @ImageNameTable TABLE
(
    ID INT IDENTITY(1,1) PRIMARY KEY,
    ImageName CHAR(30)
)
INSERT INTO @ImageNameTable
(
    ImageName
)
SELECT
   T.C.value('(.)[1]', 'varchar(80)') AS ImageName
FROM @ImageNames.nodes('/ImageNames/ImageName') AS T ( C )

SELECT 
    ImageName 
FROM @ImageNameTable

//Result：

 

ImageName
20-144-402-01.jpg             
20-144-402-02.jpg             
20-144-402-03.jpg             
20-144-402-04.jpg