如何循环XML类型并在SQL Server中每次迭代插入记录？

Question

我想创建一个具有varchar(max)参数的存储过程，该参数充当我在我的应用程序中构建的XML字符串的容器。我设法想出这将作为varchar(max)参数的存储过程通过，将转换为XML的XML字符串：

<SurveyQuestion> 
    <Name>What is your pets name?</Name> 
    <Type>1</Type> 
    <IsRequired>True</IsRequired> 
    <Answer></Answer> 
</SurveyQuestion> 
<SurveyQuestion> 
    <Name>What is your gender?</Name> 
    <Type>3</Type> 
    <IsRequired>True</IsRequired> 
    <Answer>Male</Answer> 
    <Answer>Female</Answer> 
    <Answer>Trans</Answer> 
</SurveyQuestion> 
<SurveyQuestion> 
    <Name>Which colors do you like?</Name> 
    <Type>4</Type> 
    <IsRequired>False</IsRequired> 
    <Answer>Yellow</Answer> 
    <Answer>Green</Answer> 
    <Answer>Red</Answer> 
    <Answer>Blue</Answer> 
    <Answer>Orange</Answer> 
</SurveyQuestion> 
<SurveyQuestion> 
    <Name>Rate the service that you have receive from 1 to 5. I being the lowest and 5 being the highest</Name> 
    <Type>2</Type> 
    <IsRequired>True</IsRequired> 
    <Answer>1</Answer> 
    <Answer>2</Answer> 
    <Answer>3</Answer> 
    <Answer>4</Answer> 
    <Answer>5</Answer> 
</SurveyQuestion> 

我有Questions和Answers表：

CREATE TABLE [dbo].[Questions] 
(
    [Id] [bigint] IDENTITY(1,1) NOT NULL, 
    [Question] [varchar](max) NULL, --Name 
    [IsRequired] bit NULL, 
    [SurveyFieldObjectId] [bigint] NULL --Type 
) 

CREATE TABLE [dbo].[Answers] 
(
    [Id] [bigint] IDENTITY(1,1) NOT NULL, 
    [QuestionId] [bigint] NULL, --Foreign key Questions 
    [Options] [varchar](50) NULL, --Answer 
    [SurveyFieldObjectId] [bigint] NULL 
) 

这是我的存储过程：

CREATE PROCEDURE 
    @otherIrrelevantProperty nvarchar(120), 
    @varcharXML varchar(max) 
AS 
BEGIN 
    BEGIN TRY 
     DECLARE @questionsXML = CAST(@varcharXML AS XML) 

     BEGIN TRANSACTION INSERTSURVEY 
      LOOP(@questionsXML.question) 
     BEGIN 
      INSERT INTO Questions VALUES (..) 

      LOOP(@questionsXML.answers) 
      BEGIN 
       INSERT INTO Answers VALUES (..) 
      END 
     END 

     COMMIT TRANSACTION INSERTSURVEY 

     SELECT 1 
    END TRY 
    BEGIN CATCH 
     IF (@@TRANCOUNT > 0) 
     BEGIN 
      ROLLBACK TRANSACTION INSERTSURVEY 
     END 

     SELECT -1 
    END CATCH 
END 

Answer 1

如果Name是在你的XML独一无二的，你可以做到这一点采用t wo INSERT和临时表：

CREATE TABLE #Questions 
(
    [Id] [bigint] IDENTITY(1,1) NOT NULL, 
    [Question] [varchar](max) NULL --Name 
) 

CREATE TABLE #Answers 
(
    [Id] [bigint] IDENTITY(1,1) NOT NULL, 
    [QuestionId] [bigint] NULL, --Foreign key Questions 
    [Options] [varchar](50) NULL --Answer 
) 

declare @xml xml = '<root> 
<SurveyQuestion> 
    <Name>What is your pets name?</Name> 
    <Type>1</Type> 
    <IsRequired>True</IsRequired> 
    <Answer></Answer> 
</SurveyQuestion> 
<SurveyQuestion> 
    <Name>What is your gender?</Name> 
    <Type>3</Type> 
    <IsRequired>True</IsRequired> 
    <Answer>Male</Answer> 
    <Answer>Female</Answer> 
    <Answer>Trans</Answer> 
</SurveyQuestion> 
<SurveyQuestion> 
    <Name>Which colors do you like?</Name> 
    <Type>4</Type> 
    <IsRequired>False</IsRequired> 
    <Answer>Yellow</Answer> 
    <Answer>Green</Answer> 
    <Answer>Red</Answer> 
    <Answer>Blue</Answer> 
    <Answer>Orange</Answer> 
</SurveyQuestion> 
<SurveyQuestion> 
    <Name>Rate the service that you have receive from 1 to 5. I being the lowest and 5 being the highest</Name> 
    <Type>2</Type> 
    <IsRequired>True</IsRequired> 
    <Answer>1</Answer> 
    <Answer>2</Answer> 
    <Answer>3</Answer> 
    <Answer>4</Answer> 
    <Answer>5</Answer> 
</SurveyQuestion> 
</root>'; 

CREATE TABLE #temp 
(
    [Id] [bigint] NOT NULL, 
    [Question] [varchar](8000) NOT NULL, --Name, UNIQUE!!! 
    CONSTRAINT UC_Question UNIQUE(Question) 
); 

insert into #Questions (Question) 
    output INSERTED.Id, INSERTED.Question 
    into #temp (id, question) 
    select n.value('.', 'varchar(max)') Name 
     from @xml.nodes('/root/SurveyQuestion/Name') xml(n); 

insert into #Answers (QuestionId, Options) 
    select t.Id, n.value('.', 'varchar(50)') answer 
     from #temp t 
     cross apply @xml.nodes('/root/SurveyQuestion[Name = sql:column("t.question")]/Answer') answers(n); 

select * from #Questions; 
select * from #Answers; 

drop table #Questions; 
drop table #Answers; 
drop table #temp;