CODEWITHSUNDEEP
sqlxml
Indika Pathirage
Indika Pathirage

Reputation: 29

insert an xml payload into a column as it is

I want to insert the following xml payload into a table column. 

<PostTransAuctionBidsResponse>
  <AuctionBidsResponse>
    <AuctionBidOutput>
      <BidOfferRef>-1</BidOfferRef>
      <Reason>xxx : Provider/Auction/Customer is not valid'</Reason>
      <Provider>HGM</Provider>
    </AuctionBidOutput>
  </AuctionBidsResponse>
</PostTransAuctionBidsResponse>

when I try to insert this, I was forced to convert this into a varchar value. but when I convert this, the value in the table looks like following. 

<PostTransAuctionBidsResponse><AuctionBidsResponse><AuctionBidOutput><BidOfferRef>-1</BidOfferRef><Reason>xxx : Provider/Auction/Customer is not valid'</Reason><Provider>HGM</Provider></AuctionBidOutput></AuctionBidsResponse></PostTransAuctionBidsResponse>

is it possible to change this to look like the above payload?

what I am doing is something like- 

declare @xml xml
set @xml = 
'
    <PostTransAuctionBidsResponse>
      <AuctionBidsResponse>
        <AuctionBidOutput>
          <BidOfferRef>-1</BidOfferRef>
          <Reason>xxx : Provider/Auction/Customer is not valid'</Reason>
          <Provider>HGM</Provider>
        </AuctionBidOutput>
      </AuctionBidsResponse>
    </PostTransAuctionBidsResponse>
' 

declare @Content varchar(max)
set @Content = convert(varchar(max),@xml)

insert into TES_Log(content)
values(@Content)

Upvotes: 0

Views: 233

Answers (1)

Jens Krogsboell
Jens Krogsboell

Reputation: 1123

According to this article (link) you need to explicitly convert also when assigning the value to your @xml variable. And in both cases you also need to include the third argument to convert():

set @xml = convert(XML,
'
<PostTransAuctionBidsResponse>
  <AuctionBidsResponse>
    <AuctionBidOutput>
      <BidOfferRef>-1</BidOfferRef>
      <Reason>xxx : Provider/Auction/Customer is not valid'</Reason>
      <Provider>HGM</Provider>
    </AuctionBidOutput>
  </AuctionBidsResponse>
</PostTransAuctionBidsResponse>
', 1)

set @Content = convert(varchar(max),@xml, 1)

Upvotes: 1

Related Questions