目的通过解析 XML 获取QuestionID,QuestionTypeID,OptionTextSP:create proc dbo.TestSaveSurvey(@XML ntext)asbegin declare @doc int declare @nError int set @nError=50001 --sp_xml_preparedocument要读取的XML文档时@XML, exec sp_xml_preparedocument @doc output,@xml --通过OpenXML获取行集视图, --/UIResponse/SurveyDetails/s/p/q/o 表示我要出来的节点 select * into #NullOptionTemp from openxml(@doc,'/UIResponse/SurveyDetails/s/p/q/o',1) /* QuestionID 列名称 int 数据类型 ../@QuestionId:Xpath，将那些节点映射到列 */ with ( QuestionId int '../@QuestionId', QuestionTypeId int '../@QuestionTypeId', OptionText nvarchar(100) '@OptionText' ) where OptionText is null and QuestionTypeid in(2,3,4,5,6,10) if exists(select * from #NullOptionTemp) begin set @nError=50003 goto error end delete from #NullOptionTemp error: exec sp_xml_removedocument @doc return @nError endExec TestSaveSurvey '<?xml-stylesheet type=''text/xsl'' href=''styles/GenericPage.xslt''?><UIResponse Page="Survey" Action="Edit"><UserInfo><u UserId="1304" UserLoginName="fareast/v-guohu" UserFullName="Guo Hu"><r RoleId="1" /></u></UserInfo><SurveyDetails><s SurveyId="2253" SurveyName="The Questions have no options in the survey" SurveyDescription="The Questions have no options in the survey description" CategoryId="0" LanguageId="6" StatusId="11" SurveyFriendlyName="question_have_no_options" SurveyTitle="The Questions have no options in the survey" IntroductionText="" ThankYouText="" GraphicUrl="" GraphicAltText="" RequiredExplanationText="" RequiredErrorText="" PreviousButtonText="" NextButtonText="" CloseButtonText="Close" SkipButtonText="Skip" FinishButtonText="Finish" CancelButtonText="Cancel" XslTemplatePath="" AcceptButtonText="Accept" DeclineButtonText="" OptOutText="" TrackingText="" ExpireTime="0" Updatable="1" Publishable="1" AnalyticsReadable="1" SurveySiteName="GN"><p PageId="132" PageName="Page: -1" NextPageId="0"><q QuestionId="13111" QuestionName="test question" QuestionTypeId="3" QuestionRequiredFlag="0" QuestionText="Do you like to have this survey?" VariableName="" Randomization="0" NumberOfColumns="0" DontKnowFlag="0"><o OptionId="19948" OptionText="like it!" NonSelect="0" OptionValue="0" VariableName="" PipingVariableName="" Fixed="0" Exclusive="0" /><o OptionId="19949" OptionText="Don''t like it!" NonSelect="0" OptionValue="1" VariableName="" PipingVariableName="" Fixed="0" Exclusive="0" /></q><q QuestionId="13117" QuestionName="new radio question" QuestionTypeId="3" QuestionRequiredFlag="0" QuestionText="" VariableName="" Randomization="0" NumberOfColumns="0" DontKnowFlag="0"><o OptionValue="0" VariableName="" PipingVariableName="" Fixed="0" Exclusive="0" /></q></p></s></SurveyDetails></UIResponse>'
