CODEWITHSUNDEEP
sql-serverxmlsql-server-2012
Pedram
Pedram

Reputation: 6508

Convert XML Varchar Data to Image Type in SQL Server

I'm working on Reporting Service.

I need to update all reports, so I am converting all reports Content from Image type to XML type.

It works perfectly fine.

I have tried below, but it's not converting properly.

SELECT CONVERT(VARCHAR(MAX), CONVERT(VARBINARY(MAX), REPLACE(XMLDATA,'<FontFamily>1</FontFamily>','<FontFamily>2</FontFamily>')))  FROM #a

Here, this contains my updated XML Data.

REPLACE(XMLDATA,'<FontFamily>1</FontFamily>','<FontFamily>2</FontFamily>'))

In short, I want to do many replacement in same XML data

For example, I have data something like below,

<Textbox Name="ID">
  <CanGrow>true</CanGrow>
  <KeepTogether>true</KeepTogether>
  <Paragraphs>
    <Paragraph>
      <TextRuns>
        <TextRun>
          <Value></Value>
          <Style>
            <FontFamily>Calibri</FontFamily>
            <FontSize>8pt</FontSize>
          </Style>
        </TextRun>
      </TextRuns>
      <Style>
        <TextAlign>Right</TextAlign>
      </Style>
    </Paragraph>
  </Paragraphs>
  <rd:DefaultName></rd:DefaultName>
</Textbox>

Here I need to replace font family as well as font size and many more.

So how can I do many replacement into XML data? Can you anyone please guide me.

Upvotes: 0

Views: 1115

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

The data type IMAGE is deprecated...

As is looks you are not converting to XML but to (N)VARCHAR. One can modify XML data on string level using string methods like replace, but - in most cases - it's better to use real XML with XML DML methods...

This small example should show you all casts you might need:

--this is a mock of your table with the IMAGE column with two rows
DECLARE @tbl TABLE(ID INT,XmlData IMAGE);
INSERT INTO @tbl VALUES
 (1,'<root><SomeData rowID="1"><FontFamily>1</FontFamily></SomeData></root>')
,(2,'<root><SomeData rowID="2"><FontFamily>2</FontFamily></SomeData></root>');

--The XmlData is casted to VARCHAR(MAX) as well as to XML
--The result is filled into a new table #tmpTable
SELECT ID
      ,XmlData AS ImageType
      ,CAST(XmlData AS VARBINARY(MAX)) AS VarbinaryType
      ,CAST(CAST(XmlData AS VARBINARY(MAX)) AS VARCHAR(MAX)) AS VarcharMaxType
      ,CAST(CAST(XmlData AS VARBINARY(MAX)) AS XML) AS XmlType 
INTO #tmpTable
FROM @tbl;

--this is the actual content
SELECT * FROM #tmpTable;

--modify with string method
UPDATE #tmpTable SET VarcharMaxType=REPLACE(VarcharMaxType,'<FontFamily>1</FontFamily>','<FontFamily>99</FontFamily>');

--modify with XML-DML method
UPDATE #tmpTable SET XmlType.modify('replace value of (/root/SomeData[@rowID=1]/FontFamily/text())[1] with 99');

--the updated content
SELECT * FROM #tmpTable;

--and all converted back to IMAGE
SELECT ID
      ,ImageType
      ,CAST(VarcharMaxType AS IMAGE) AS VarcharMaxAsIMAGE
      ,CAST(CAST(XmlType AS VARCHAR(MAX)) AS IMAGE) AS XmlTypeAsIMAGE
FROM #tmpTable;
GO

--Clean up
DROP TABLE #tmpTable;

Upvotes: 1

Related Questions