SQL SERVER XML modify OUTPUT add NameSpace and root

Question

I need to modify my XML in SQL Server 2016 to add a Namespace and a couple of header tags. Currently I have a Select Query That gives me this block

Select @MAXML, @TRANSXML,@HOSTSETTINGSXML FOR XML PATH

  <merchantAuthentication>
  <name>myName</name>
  <transactionKey>myKey</transactionKey>
 </merchantAuthentication>
      <transactionRequest>
    <amount>5.500000000000000e+000</amount>
  </transactionRequest>
  <hostedPaymentSettings>
    <settingName>hostedPaymentBillingAddressOptions</settingName>
    <settingValue>{"show": true, "required":true}</settingValue>
  </hostedPaymentSettings>
  <hostedPaymentSettings>
    <settingName>hostedPaymentButtonOptions</settingName>
    <settingValue>{"text": "Pay"}</settingValue>
  </hostedPaymentSettings>
  <hostedPaymentSettings>
    <settingName>hostedPaymentReturnOptions</settingName>
    <settingValue>anyValue</settingValue>
  </hostedPaymentSettings>

And this should be my output:

<Request>
    <Body>
        <Xml>
            <getHostedPaymentPageRequest xmlns="AnetApi/xml/v1/schema/AnetApiSchema.xsd">
                 My xml from above
        </getHostedPaymentPageRequest>
    </Xml>
</Body>
</Request>

I am stuck. tried a number of times but could not get the exact xml. Thanks is advance.

Baibhav Ghimire · Accepted Answer · 2017-11-16 06:14:53Z

1

I will give my own answer, it might be helpful to others. Step1: Convert entire XML to string and add headers as string Step2: Convert the new string back to XML

Step1:

Set @strXML = '<Request><Body><Xml><getHostedPaymentPageRequest 
xmlns="AnetApi/xml/v1/schema/AnetApiSchema.xsd">'+
                Convert(nvarchar(MAX),@x)+
                 '</getHostedPaymentPageRequest></Xml></Body></Request>'

Step2:

SET @RequestXML = (SELECT CONVERT(XML,(SELECT CAST(@strXML AS XML))))

answered Nov 16, 2017 at 6:14

Baibhav Ghimire

231 silver badge5 bronze badges

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

Collectives™ on Stack Overflow

SQL SERVER XML modify OUTPUT add NameSpace and root

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related