Using of Xpath in SQL Server

Question

I have written a stored procedure which would query through an xml and return value based on the Xpath passed to it. I have given a dummy xml data to debug. The stored procedure gets created, but when I debug it, it says must declare scalar variable @xml. I do not know where I am going wrong exactly.

I am sharing the stored procedure code for more reference.

spXML 
'Gender','''/CreateAndSendMessageRequest/CompositionRequest/PolicyDetails
/MainPolicyHolder/PolicyHolderDetails'''

ALTER PROCEDURE spXML 
    @lasttag VARCHAR(50),
    @root VARCHAR(MAX) 
AS
BEGIN
    Declare @xml xml 
    Set @xml = '<?xml version="1.0" encoding="utf-8"?>
                <CreateAndSendMessageRequest>
                    <CompositionRequest>
                        <Metadata>
                            <PolicyReference>250028766622DN</PolicyReference>
                            <AccountReference>Test1234</AccountReference>
                            <QuoteReference>Test3214</QuoteReference>
                            <OutboundTransactionID>string</OutboundTransactionID>
                            <InboundActivityID>string</InboundActivityID>
                            <DocumentName>DocumentA127</DocumentName>
                            <DocumentID />
                            <CommunicationID>C004</CommunicationID>
                            <CorrelationID>PC: 20c14f9b-2a1b-45dc-b680-52ffceb86d05</CorrelationID>
                            <ContentType>string</ContentType>
                            <isSensitive>true</isSensitive>
                            <isReadOnly>true</isReadOnly>
                            <isDocumentInbound>true</isDocumentInbound>
                            <ChannelIdentifier>POST</ChannelIdentifier>
                            <BrandType>string</BrandType>
                            <SchemeNameCode>string</SchemeNameCode>
                            <isNotificationRequiredIndicator>false</isNotificationRequiredIndicator>
                            <DocumentPriorityIndicator>Standard</DocumentPriorityIndicator>
                            <SpecialInstructions>
                                 <Code>string</Code>
                                 <Description>string</Description>
                            </SpecialInstructions>
                            <SupressIndicator>false</SupressIndicator>
                            <OutsortIndicator>string</OutsortIndicator>
                            <DocumentIncludes>
                                <DocumentId DocumentTitle="string">string</DocumentId>
                            </DocumentIncludes>
                            <CreatedBy>string</CreatedBy>
                            <UpdatedBy>string</UpdatedBy>
                            <FulfilledDateTime>2017-05-15T07:41:13</FulfilledDateTime>
                            <UploadedDateTime>2013-12-21T17:02:42+05:30</UploadedDateTime>
                            <RequestDateTime>2016-01-01T19:07:42</RequestDateTime>
                            <UpdateDateTime>2012-01-07T19:42:56</UpdateDateTime>
                        </Metadata>
                        <PolicyDetails>
                            <PolicyReference>string</PolicyReference>
                            <QuoteReference>string</QuoteReference>
                            <PolicyVersionStartDate>2009-11-03</PolicyVersionStartDate>
                            <MainPolicyHolder>
                                <PolicyHolderDetails>
                                     <Title>string</Title>
                                     <Initials>string</Initials>
                                     <Honours>string</Honours>
                                     <Firstname>string</Firstname>
                                     <Surname>string</Surname>
                                     <FullName>string</FullName>
                                     <Gender>F</Gender>
                                     <DateOfBirth>2018-04-13</DateOfBirth>
                                 </PolicyHolderDetails>
                             </PolicyDetails>
                         </CompositionRequest>
                         <VersionInfo majorVersion="1" minorVersion="19" />
                    </CreateAndSendMessageRequest>' 


    Declare @tag varchar(max)

    set @tag = 'Select X.T.value(''('+@lasttag+')[1]'',''varchar(50)'') as Result 
                From @xml.nodes('+@root+') as X(T)'

    print @tag

    Execute(@tag)
End

Answer 1

You are attempting to run an ad hoc query like so

Declare @tag varchar(max)
set @tag = 'Select X.T.value(''('+@lasttag+')[1]'',''varchar(50)'')as 
Result 
From @xml.nodes('+@root+') as X(T)'
print @tag
Execute(@tag)

That is referencing a variable outside its scope. @xml is a variable in the stored procedure, not in the dynamic query select X.T.value.... You would need to put @xml into the ad hoc query in order to use it.