Post reply

How to create xml File in SQL

  • January 31, 2012 at 2:42 am

    #252133

    Dear All,

    I have an xml file ( format given below) which I have to create and send it to a SMS service Provider as an part of a Web Service request within SQL Server (Version 2008).

    <?xml version=”1.0” ?>

    <BSMS version=1.0>

    <request userid="yourUserId" password="yourPassword" pin="yourPin" type="push">

    <publishData>

    <fromAddress></fromAddress>

    <messageTxt>Type Your message here</messageTxt>

    <addressArray>

    <address>your mobile numbers</address>

    <address>your mobile numbers</address>

    <address>your mobile numbers</address>

    </addressArray>

    </publishData>

    <scheduleTime></scheduleTime>

    </request>

    </BSMS>

    How to do it in SQL Server?

  • January 31, 2012 at 3:28 am

    #1440463

    I think you need something like this:

    DECLARE @XMLStartTag VARCHAR(1000)

    DECLARE @XMLBody VARCHAR(MAX)

    DECLARE @XMLEndTag VARCHAR(1000)

    DECLARE @CompleteXML VARCHAR(MAX)

    SET @XMLBody = ''

    SELECT @XMLStartTag = '<?xml version=”1.0” ?><BSMS version=1.0><request userid="yourUserId" password="yourPassword" pin="yourPin" type="push">'

    SELECT @XMLEndTag = '<scheduleTime></scheduleTime></request></BSMS>'

    SELECT @XMLBody = @XMLBody + '<publishData> <fromAddress>'+TableName.FromAddress+'</fromAddress> <messageTxt>'+TableName.MessageBody+'</messageTxt> <addressArray> <address>'+TableName.ContactNumber+'</address> <address>'+TableName.ContactNumber+'</address> <address>'+TableName.ContactNumber'</address> </addressArray> </publishData>'

    FROM

    TableName

    SET @CompleteXML = @XMLStartTag + @XMLBody + @XMLEndTag

    SELECT @CompleteXML

    This may or may not be the exact code that you want but it should give you a fair idea about how to do that.

    Sujeet Singh

  • February 1, 2012 at 2:22 am

    #1441090

    <address>your mobile numbers</address>

    this will repeat on basis of how many Mobile number found.

  • February 1, 2012 at 3:10 am

    #1441103

    Subrata Bauri (2/1/2012)

    <address>your mobile numbers</address>

    this will repeat on basis of how many Mobile number found.

    With a little tweak in the above code you can achieve that. I can do that for you but without the table scripts & sample data it is not possible to provide the exact code.

    Anyways if you will do that change by yourself you will learn too 😉

    Sujeet Singh

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply