Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 430
  • Last Modified:

How do I send xml special characters (like &) to stored procedure

How do I send xml special characters (like &) to stored procedure....See the & below in T & A
Command: EXEC [dbo].[usp_wpCreateShipmentDocuments] @documents = <ShipmentDocuments><Item><SkShipmentNumber>15698</SkShipmentNumber><FormCwtCodeSKey>97</FormCwtCodeSKey><OriginPlantNumber>7019</OriginPlantNumber><GeneratorCustomerNumber>215555</GeneratorCustomerNumber><GeneratorPhone>708-499-1869</GeneratorPhone><GeneratorRegion>IL</GeneratorRegion><GeneratorMailName>T & A MOBIL SVC LTD</GeneratorMailName><GeneratorMailAddress1>10240 S PULASKI

Open in new window

0
mtiDev
Asked:
mtiDev
  • 2
2 Solutions
 
pivarCommented:
Hi,

Try this

/peter
EXEC [dbo].[usp_wpCreateShipmentDocuments] @documents = '<ShipmentDocuments><Item><SkShipmentNumber>15698</SkShipmentNumber><FormCwtCodeSKey>97</FormCwtCodeSKey><OriginPlantNumber>7019</OriginPlantNumber><GeneratorCustomerNumber>215555</GeneratorCustomerNumber><GeneratorPhone>708-499-1869</GeneratorPhone><GeneratorRegion>IL</GeneratorRegion><GeneratorMailName>T &amp; A MOBIL SVC LTD</GeneratorMailName><GeneratorMailAddress1>10240 S PULASKI'

Open in new window

0
 
Mark WillsTopic AdvisorCommented:
In XML there are a few special characters that need explicit replacement, and fortunately, XML provides a facility to be able to understand those special characters...

Basically, you need to do a replace on those reserved words / special characters at time of creating the XML string (otherwise it is not a legitimate XML packet anyway). for example the 4 special characters are ampersand, quote, less than and greater than signs:

        set @description = replace(@description,'&','&amp;')
        set @description = replace(@description,'"','&quot;')
        set @description = replace(@description,'<','<')
        set @description = replace(@description,'>','>')

0
 
Mark WillsTopic AdvisorCommented:
Ha, this site actually converted the less than and greater than which should be '& l t ;' and '& g t ;'  without the spaces....


0
 
BrandonGalderisiCommented:
If you have them in an XML datatype, it will already have &amp in it.  Also, if you cast a string that has the value &, it will change it.
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now