替换为“& lt;"和“& gt;"带有“<"和“>"在SQL Server中 [英] Replace “<” and “>” with “<” and “>” in sql server

问题描述

我是xml的新手

我有这样的查询

SELECT  ProjectId, 
       ProjectCode, 
       ProjectName, 
       TechId, 
      -- LocationId, 

      ( SELECT GeoId,PoliticalDivisionId ,GeographicLocationName,IsoCode,Longitude,Latitude,ParentLocationId,
       t2.CreatedBy,t2.CreatedOn,t2.LastUpdatedBy,t2.LastUpdatedOn
    FROM GeographicLocation t2
    WHERE GeoId = t1.LocationId
    FOR XML  PATH('Location') ),
       RtoId, 
       CreatedBy,
       CreatedOn,
       LastUpdatedBy,
       LastUpdatedOn
FROM Project t1
where ProjectId=1
FOR XML PATH('ProjectInfo')

它将xml返回为

<ProjectInfo>
<ProjectId>1</ProjectId>
<ProjectCode>US-W1-00001</ProjectCode>
<ProjectName>Rees</ProjectName>
<TechId>1</TechId>
&lt;Location&gt;&lt;GeoId&gt;235&lt;/GeoId&gt;&lt;PoliticalDivisionId&gt;2&lt;/PoliticalDivisionId&gt;&lt;GeographicLocationName&gt;UNITED STATES&lt;/GeographicLocationName&gt;&lt;IsoCode&gt;US&lt;/IsoCode&gt;&lt;/Location&gt;
<RtoId>3</RtoId>
<CreatedBy>1</CreatedBy>
<CreatedOn>2013-06-30T20:55:21.587</CreatedOn>
<LastUpdatedBy>1</LastUpdatedBy>
<LastUpdatedOn>2013-06-30T20:55:21.587</LastUpdatedOn>

prject标签以<和>.但是位置的内部标签显示为<"和>"如何用<和>

prject tags are shown in the form < and > . But inner tags of Location are shown as "<" and ">" how do I replace them with < and >

更新:问题中有一个小错误.内部xml不是用于rtoid，而是用于Location

Update : there was a small error in the question . inner xml was not for rtoid , it was for Location

我将查询更新为

SELECT  ProjectId, 
       ProjectCode, 
       ProjectName, 
       TechId, 
      -- LocationId, 

      replace(replace( ( SELECT GeoId,PoliticalDivisionId ,GeographicLocationName,IsoCode,Longitude,Latitude,ParentLocationId,
       t2.CreatedBy,t2.CreatedOn,t2.LastUpdatedBy,t2.LastUpdatedOn
    FROM GeographicLocation t2
    WHERE GeoId = t1.LocationId
    FOR XML  PATH('Location') ), '&lt;', '<'), '&gt;', '>'),
       RtoId, 
       CreatedBy,
       CreatedOn,
       LastUpdatedBy,
       LastUpdatedOn
FROM Project t1
where ProjectId=1
FOR XML PATH('ProjectInfo')

但仍然相同

推荐答案

我认为正确的方法是使用 TYPE指令

I think correct way is using TYPE Directive

SELECT  ProjectId, 
        ...,
      ( SELECT Geo, ...
        FROM GeographicLocation t2
        WHERE GeoId = t1.LocationId
        FOR XML  PATH('Location'), TYPE),
       RtoId,                      ^^^^
       ...
FROM Project t1
where ProjectId=1
FOR XML PATH('ProjectInfo') 

这篇关于替换为“&amp; lt;"和“&amp; gt;"带有“&lt;"和“&gt;"在SQL Server中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！