sql XML處理,sp_xml_preparedocument,openxml

目的通過解析 XML 擷取
QuestionID,QuestionTypeID,OptionText
SP:
create proc dbo.TestSaveSurvey
(
@XML ntext
)
as
begin
   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
end

Exec 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>

'