数据库通过表名找到表，SQL SERVER 解析XML到数据库表

2023-09-25 阅读 18 评论 0

摘要：//片段1IFOBJECT_ID('tempdb..#TEMP')ISNOTNULLBEGINDROPTABLE#TEMPENDCREATETABLE#TEMP(titleVARCHAR(40),authorVARCHAR(40),priceVARCHAR(40))DECLARE@xmlDocXMLSET@xmlDoc='<books><bookid="0001"><title>C</ti

//片段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