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

If you remove most of the content of @xml, do you get the same error? I'm wondering if it's a string formatting issue and you have an extra ' in the xml somewhere (it's hard to see with current formatting) — DiskJunky
– DiskJunky, Commented Oct 10, 2017 at 9:37

DiskJunky · Accepted Answer · 2017-10-10 10:19:15Z

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.

answered Oct 10, 2017 at 10:19

DiskJunky

5,0213 gold badges45 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Ksingh Over a year ago

I tried that too..but it returns the same error. @DiskJunky

DiskJunky Over a year ago

@Ksingh can you post what you tried? There could be another scope issue in the update

Ksingh Over a year ago

I just passed the '@xml' in the place where i am declaring other variables like 'lasttag' and '@root'

DiskJunky Over a year ago

@Ksingh I think you misunderstand what's going on. You have one layer with @xml declared. If you want to use it from within your custom query, then it must be declared within your custom query. Changing it to 'select * from ' + @xml + '... won't work. You'd need something like 'declare @xml xml; select * from @xml' to bring it within scope

Collectives™ on Stack Overflow

Using of Xpath in SQL Server

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related