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

Posted on 2009-05-01
Last Modified: 2012-05-06
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

Question by:mtiDev
    LVL 22

    Expert Comment


    Try this

    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

    LVL 51

    Accepted Solution

    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,'>','>')

    LVL 51

    Assisted Solution

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

    LVL 39

    Expert Comment

    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.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    C# primary key 9 46
    SQL Dynamic Insert 18 43
    SQL Agent Timeout 5 27
    How to query LOCK_ESCALATION 4 24
    I recently came across an interesting Question In EE ( and was puzzled about how to achieve that using SSIS out of the box tasks, which was i…
    Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now