Inccorect Syntax near '/'

I have written a stored procedure which would query a piece of XML and return values based on the Xpath passed to it. On passing dummy data to the stored procedure for debugging, it works fine.

Now, I am passing the parameters to the stored procedure from code. As a result I get an exception

Incorrect Syntax near '/'

I don't know if there is a problem in the Xml orthe Xpath because when I pass the same xml and Xpath to the stored procedure and execute it, it works fine and returns the desired result.

This is the stored procedure:

ALTER PROCEDURE spXML
    @lasttag VARCHAR(50),
    @root VARCHAR(MAX),
    @incomingxml VARCHAR(MAX)
AS
BEGIN
    DECLARE @xml XML
    SET @xml = @incomingxml

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

    PRINT @tag

    EXECUTE sp_executesql @tag, N'@xml XML', @xml;
END

This is my C# code:

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Generic.DAL.XmlParser
{
    public class XMLParserDAL : IXmlParserDAL
    {
        public string GetXmlNodeValue(string fieldfieldIdentifier, string lasttag, 
        string xmlcheck)
        {
            string value = string.Empty;
            try
            {
                using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
                {
                    conn.Open();
                    SqlCommand cmd = new SqlCommand("spXML", conn);
                    cmd.CommandType = CommandType.StoredProcedure;

                    cmd.Parameters.Add(new SqlParameter("@root", fieldfieldIdentifier));
                    cmd.Parameters.Add(new SqlParameter("@lasttag", lasttag));
                    cmd.Parameters.Add(new SqlParameter("@incomingxml", xmlcheck));

                    DataSet ds = new DataSet();
                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                    da.Fill(ds);

                    foreach(DataRow dr in ds.Tables[0].Rows)
                    {
                        value = Convert.ToString(dr["Result"]);
                    }
                 }
             }
             catch (Exception ex)
             {
                 ex.HelpLink = "DataLevel";
                 throw;
             }

             return value;
         }
     }
}

This is the 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>
        <Fulfilments>
            <Fulfilment>
                <channel>POST</channel>
                <Correspondence>
                    <MainRecipient>
                        <Title>string</Title>
                        <Initials>string</Initials>
                        <Honours>string</Honours>
                        <Firstname>string</Firstname>
                        <Surname>string</Surname>
                        <FullName>string</FullName>
                        <Gender>NA</Gender>
                        <DateOfBirth>2018-02-04</DateOfBirth>
                    </MainRecipient>
                    <JointRecipient>
                        <Title>string</Title>
                        <Initials>string</Initials>
                        <Honours>string</Honours>
                        <Firstname>string</Firstname>
                        <Surname>string</Surname>
                        <FullName>string</FullName>
                        <Gender>F</Gender>
                        <DateOfBirth>2005-09-12</DateOfBirth>
                    </JointRecipient>
                    <CorrespondenceTelephone>
                        <MobileNumber>07401707984</MobileNumber>
                        <LandlineNumber>0120232325</LandlineNumber>
                        <WorkNumber>01202667788</WorkNumber>
                    </CorrespondenceTelephone>
                    <CorrespondenceEmailAddress>
                        <EmailAddress>dinesh.kumar@saggezza.com</EmailAddress>
                    </CorrespondenceEmailAddress>
                    <CorrespondenceAddress>
                        <AdditionalLine>string</AdditionalLine>
                        <OrganisationName>string</OrganisationName>
                        <DepartmentName>string</DepartmentName>
                        <POBoxNumber>string</POBoxNumber>
                        <SubBuildingName>string</SubBuildingName>
                        <BuildingName>string</BuildingName>
                        <BuildingNumber>string</BuildingNumber>
                        <DependantThoroughfare>string</DependantThoroughfare>
                        <Thoroughfare>string</Thoroughfare>
                        <DoubleDependentLocality>string</DoubleDependentLocality>
                        <DependentLocality>string</DependentLocality>
                        <PostTown>string</PostTown>
                        <Postcode>string</Postcode>
                        <ConcatenationIND>false</ConcatenationIND>
                        <ForeignPostcodeIND>true</ForeignPostcodeIND>
                        <ChannelIslndPostcodeIND>true</ChannelIslndPostcodeIND>
                    </CorrespondenceAddress>
                </Correspondence>
            </Fulfilment>
        </Fulfilments>
        <PolicyDetails>
            <PolicyReference>string</PolicyReference>
            <QuoteReference>string</QuoteReference>
            <PolicyVersionStartDate>2009-11-03</PolicyVersionStartDate>
            <PolicyVersionStartTime>01:11:15.0000000+05:30</PolicyVersionStartTime>
            <PolicyVersionEndDate>2017-08-17</PolicyVersionEndDate>
            <PolicyVersionEndTime>03:07:57.0000000+05:30</PolicyVersionEndTime>
            <PolicyStartDate>2000-07-29</PolicyStartDate>
            <PolicyStartTime>00:50:52.0000000+05:30</PolicyStartTime>
            <PolicyEffectiveStartDate>2016-02-07</PolicyEffectiveStartDate>
            <PolicyEffectiveStartTime>22:42:38.0000000+05:30</PolicyEffectiveStartTime>
            <PolicyEndDate>2000-08-23</PolicyEndDate>
            <PolicyEndTime>23:04:46.0000000+05:30</PolicyEndTime>
            <PolicyPerceptionDate>2008-07-09T04:26:36</PolicyPerceptionDate>
            <PreviousPolicyEndDate>2008-02-06</PreviousPolicyEndDate>
            <PolicyAge>100</PolicyAge>
            <PolicyStatus>
                <Code>Code1</Code>
                <Description>Descripition1</Description>
            </PolicyStatus>
            <TypeOfTransaction>string</TypeOfTransaction>
            <MainPolicyHolder>
                <PolicyHolderDetails>
                     <Title>string</Title>
                     <Initials>string</Initials>
                     <Honours>string</Honours>
                     <Firstname>string</Firstname>
                     <Surname>string</Surname>
                     <FullName>Ganesh</FullName>
                     <Gender>M</Gender>
                     <DateOfBirth>2018-04-13</DateOfBirth>
                     <PolicyHolderDetails>
                         <MaritalStatus>
                             <Code>string</Code>
                             <Description>string</Description>
                         </MaritalStatus>
                         <CorrespondenceAddress>
                              <AdditionalLine>string</AdditionalLine>
                              <OrganisationName>string</OrganisationName>
                              <DepartmentName>string</DepartmentName>
                              <POBoxNumber>string</POBoxNumber>
                              <SubBuildingName>string</SubBuildingName>
                              <BuildingName>string</BuildingName>
                              <BuildingNumber>string</BuildingNumber>
                              <DependantThoroughfare>string</DependantThoroughfare>
                              <Thoroughfare>string</Thoroughfare>
                              <DoubleDependentLocality>string</DoubleDependentLocality>
                              <DependentLocality>string</DependentLocality>
                              <PostTown>string</PostTown>
                              <Postcode>string</Postcode>
                              <ConcatenationIND>true</ConcatenationIND>
                              <ForeignPostcodeIND>true</ForeignPostcodeIND>
                              <ChannelIslndPostcodeIND>false</ChannelIslndPostcodeIND>
                          </CorrespondenceAddress>
                          <TelephoneDetails>
                              <MobileNumber>string</MobileNumber>
                              <LandlineNumber>string</LandlineNumber>
                              <WorkNumber>string</WorkNumber>
                          </TelephoneDetails>
                          <EmailAddress>
                               <EmailAddress>string</EmailAddress>
                          </EmailAddress>
                          <IsHomeOwner>true</IsHomeOwner>
                          <IsDeceased>true</IsDeceased>
                          <StaffIndicator>true</StaffIndicator>
                          <LVMemberIndicator>false</LVMemberIndicator>
                          <PreviousStaffIndicator>true</PreviousStaffIndicator>
                          <NumberOfVehiclesInHousehold>201</NumberOfVehiclesInHousehold>
                          <NumberOfResidencyYearsInUK>string</NumberOfResidencyYearsInUK>
            </MainPolicyHolder>
            <JointPolicyHolder>
                <PolicyHolderDetails>
                    <Title>string</Title>
                    <Initials>string</Initials>
                    <Honours>string</Honours>
                    <Firstname>string</Firstname>
                    <Surname>string</Surname>
                    <FullName>string</FullName>
                    <Gender>M</Gender> 
                    <DateOfBirth>2009-07-28</DateOfBirth>
                </PolicyHolderDetails>
                <MaritalStatus>
                    <Code>string</Code>
                    <Description>string</Description>
                </MaritalStatus>
                <IsHomeOwner>false</IsHomeOwner>
                <IsDeceased>true</IsDeceased>
                <StaffIndicator>true</StaffIndicator>
                <LVMemberIndicator>false</LVMemberIndicator>
                <PreviousStaffIndicator>false</PreviousStaffIndicator>
                <NumberOfVehiclesInHousehold>201</NumberOfVehiclesInHousehold>
                <NumberOfResidencyYearsInUK>string</NumberOfResidencyYearsInUK>
            </JointPolicyHolder>
            <PolicyInceptionChannel>
                <Code>Web</Code>
                <Description>Call Centre</Description>
            </PolicyInceptionChannel>
            <PolicyLatestChannel>
                 <Code>string</Code>
                 <Description>string</Description>
            </PolicyLatestChannel>
            <PolicyProduct>
                 <Code>TestCode</Code>
            </PolicyProduct>
            <AdjustmentReason>
                <TransferOfVehicleIndicator>false</TransferOfVehicleIndicator>
                <TransferOfInterestIndicator>false</TransferOfInterestIndicator>
            </AdjustmentReason>   
            <MultiProductDiscountAmount>1000.00</MultiProductDiscountAmount>
            <MultiProductDiscountIND>true</MultiProductDiscountIND>
            <IPTPayableIND>true</IPTPayableIND>
            <TotalAnnualInsurancePrice>1000.00</TotalAnnualInsurancePrice>
        </PolicyDetails>
    </CompositionRequest>
    <VersionInfo majorVersion="1" minorVersion="19" />
</CreateAndSendMessageRequest>

1 answer

  • answered 2017-10-11 10:58 phuzi

    Whilst trying to format the sample XML it appears to be incorrectly formatted!

    There's a PolicyHolderDetails section that seems to have a closing tag without the leading /.

    See comments (<!-- -->) in formatted XML below (about half way down.)

    <?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>
            <Fulfilments>
                <Fulfilment>
                    <channel>POST</channel>
                    <Correspondence>
                        <MainRecipient>
                            <Title>string</Title>
                            <Initials>string</Initials>
                            <Honours>string</Honours>
                            <Firstname>string</Firstname>
                            <Surname>string</Surname>
                            <FullName>string</FullName>
                            <Gender>NA</Gender>
                            <DateOfBirth>2018-02-04</DateOfBirth>
                        </MainRecipient>
                        <JointRecipient>
                            <Title>string</Title>
                            <Initials>string</Initials>
                            <Honours>string</Honours>
                            <Firstname>string</Firstname>
                            <Surname>string</Surname>
                            <FullName>string</FullName>
                            <Gender>F</Gender>
                            <DateOfBirth>2005-09-12</DateOfBirth>
                        </JointRecipient>
                        <CorrespondenceTelephone>
                            <MobileNumber>07401707984</MobileNumber>
                            <LandlineNumber>0120232325</LandlineNumber>
                            <WorkNumber>01202667788</WorkNumber>
                        </CorrespondenceTelephone>
                        <CorrespondenceEmailAddress>
                            <EmailAddress>dinesh.kumar@saggezza.com</EmailAddress>
                        </CorrespondenceEmailAddress>
                        <CorrespondenceAddress>
                            <AdditionalLine>string</AdditionalLine>
                            <OrganisationName>string</OrganisationName>
                            <DepartmentName>string</DepartmentName>
                            <POBoxNumber>string</POBoxNumber>
                            <SubBuildingName>string</SubBuildingName>
                            <BuildingName>string</BuildingName>
                            <BuildingNumber>string</BuildingNumber>
                            <DependantThoroughfare>string</DependantThoroughfare>
                            <Thoroughfare>string</Thoroughfare>
                            <DoubleDependentLocality>string</DoubleDependentLocality>
                            <DependentLocality>string</DependentLocality>
                            <PostTown>string</PostTown>
                            <Postcode>string</Postcode>
                            <ConcatenationIND>false</ConcatenationIND>
                            <ForeignPostcodeIND>true</ForeignPostcodeIND>
                            <ChannelIslndPostcodeIND>true</ChannelIslndPostcodeIND>
                        </CorrespondenceAddress>
                    </Correspondence>
                </Fulfilment>
            </Fulfilments>
            <PolicyDetails>
                <PolicyReference>string</PolicyReference>
                <QuoteReference>string</QuoteReference>
                <PolicyVersionStartDate>2009-11-03</PolicyVersionStartDate>
                <PolicyVersionStartTime>01:11:15.0000000+05:30</PolicyVersionStartTime>
                <PolicyVersionEndDate>2017-08-17</PolicyVersionEndDate>
                <PolicyVersionEndTime>03:07:57.0000000+05:30</PolicyVersionEndTime>
                <PolicyStartDate>2000-07-29</PolicyStartDate>
                <PolicyStartTime>00:50:52.0000000+05:30</PolicyStartTime>
                <PolicyEffectiveStartDate>2016-02-07</PolicyEffectiveStartDate>
                <PolicyEffectiveStartTime>22:42:38.0000000+05:30</PolicyEffectiveStartTime>
                <PolicyEndDate>2000-08-23</PolicyEndDate>
                <PolicyEndTime>23:04:46.0000000+05:30</PolicyEndTime>
                <PolicyPerceptionDate>2008-07-09T04:26:36</PolicyPerceptionDate>
                <PreviousPolicyEndDate>2008-02-06</PreviousPolicyEndDate>
                <PolicyAge>100</PolicyAge>
                <PolicyStatus>
                    <Code>Code1</Code>
                    <Description>Descripition1</Description>
                </PolicyStatus>
                <TypeOfTransaction>string</TypeOfTransaction>
                <MainPolicyHolder>
                    <PolicyHolderDetails>
                        <Title>string</Title>
                        <Initials>string</Initials>
                        <Honours>string</Honours>
                        <Firstname>string</Firstname>
                        <Surname>string</Surname>
                        <FullName>Ganesh</FullName>
                        <Gender>M</Gender>
                        <DateOfBirth>2018-04-13</DateOfBirth>
                    <PolicyHolderDetails>
    <!--             ^ Missing / here -->
                    <MaritalStatus>
                        <Code>string</Code>
                        <Description>string</Description>
                    </MaritalStatus>
                    <CorrespondenceAddress>
                        <AdditionalLine>string</AdditionalLine>
                        <OrganisationName>string</OrganisationName>
                        <DepartmentName>string</DepartmentName>
                        <POBoxNumber>string</POBoxNumber>
                        <SubBuildingName>string</SubBuildingName>
                        <BuildingName>string</BuildingName>
                        <BuildingNumber>string</BuildingNumber>
                        <DependantThoroughfare>string</DependantThoroughfare>
                        <Thoroughfare>string</Thoroughfare>
                        <DoubleDependentLocality>string</DoubleDependentLocality>
                        <DependentLocality>string</DependentLocality>
                        <PostTown>string</PostTown>
                        <Postcode>string</Postcode>
                        <ConcatenationIND>true</ConcatenationIND>
                        <ForeignPostcodeIND>true</ForeignPostcodeIND>
                        <ChannelIslndPostcodeIND>false</ChannelIslndPostcodeIND>
                    </CorrespondenceAddress>
                    <TelephoneDetails>
                        <MobileNumber>string</MobileNumber>
                        <LandlineNumber>string</LandlineNumber>
                        <WorkNumber>string</WorkNumber>
                    </TelephoneDetails>
                    <EmailAddress>
                        <EmailAddress>string</EmailAddress>
                    </EmailAddress>
    <!--             ^ This bit looks wrong too -->
                    <IsHomeOwner>true</IsHomeOwner>
                    <IsDeceased>true</IsDeceased>
                    <StaffIndicator>true</StaffIndicator>
                    <LVMemberIndicator>false</LVMemberIndicator>
                    <PreviousStaffIndicator>true</PreviousStaffIndicator>
                    <NumberOfVehiclesInHousehold>201</NumberOfVehiclesInHousehold>
                    <NumberOfResidencyYearsInUK>string</NumberOfResidencyYearsInUK>
                </MainPolicyHolder>
                <JointPolicyHolder>
                    <PolicyHolderDetails>
                        <Title>string</Title>
                        <Initials>string</Initials>
                        <Honours>string</Honours>
                        <Firstname>string</Firstname>
                        <Surname>string</Surname>
                        <FullName>string</FullName>
                        <Gender>M</Gender>
                        <DateOfBirth>2009-07-28</DateOfBirth>
                    </PolicyHolderDetails>
                    <MaritalStatus>
                        <Code>string</Code>
                        <Description>string</Description>
                    </MaritalStatus>
                    <IsHomeOwner>false</IsHomeOwner>
                    <IsDeceased>true</IsDeceased>
                    <StaffIndicator>true</StaffIndicator>
                    <LVMemberIndicator>false</LVMemberIndicator>
                    <PreviousStaffIndicator>false</PreviousStaffIndicator>
                    <NumberOfVehiclesInHousehold>201</NumberOfVehiclesInHousehold>
                    <NumberOfResidencyYearsInUK>string</NumberOfResidencyYearsInUK>
                </JointPolicyHolder>
                <PolicyInceptionChannel>
                    <Code>Web</Code>
                    <Description>Call Centre</Description>
                </PolicyInceptionChannel>
                <PolicyLatestChannel>
                    <Code>string</Code>
                    <Description>string</Description>
                </PolicyLatestChannel>
                <PolicyProduct>
                    <Code>TestCode</Code>
                </PolicyProduct>
                <AdjustmentReason>
                    <TransferOfVehicleIndicator>false</TransferOfVehicleIndicator>
                    <TransferOfInterestIndicator>false</TransferOfInterestIndicator>
                </AdjustmentReason>
                <MultiProductDiscountAmount>1000.00</MultiProductDiscountAmount>
                <MultiProductDiscountIND>true</MultiProductDiscountIND>
                <IPTPayableIND>true</IPTPayableIND>
                <TotalAnnualInsurancePrice>1000.00</TotalAnnualInsurancePrice>
            </PolicyDetails>
        </CompositionRequest>
        <VersionInfo majorVersion="1" minorVersion="19"/>
    </CreateAndSendMessageRequest>