我在SQL列中有多行文本，我正在从SQL查询中获取数据。我的查询是在每行末端插入。
查询：
Declare @vcFooterText Varchar(max)
Select @vcFooterText = (Select replace(replace(AppConfigValue,CHAR(13),'
'),CHAR(10),'
') From COM.Config_Application With (NoLock) Where AppConfigId = 'PackingNoteFooterText')
Select @vcFooterText
查询结果：
Line 1
Line 2 
Line 3
然后使用XML显示数据：
Select tbl.OrderNumber, Cast(
(
'<Documents><Document><PackingNote>' +
Cast((
Select
CustomerName CustomerName,
Convert(Varchar(10), OrderCreatedOn, 103) + ' ' + Convert(Varchar(5), OrderCreatedOn, 114) As OrderDate,
CustomerNumber CustomerNumber,
OrderNumber OrderNumber,
BatchNumber BatchNumber,
ChannelOrderNumber ChannelOrderNumber,
OrderChannel OrderChannel,
Case
When DM.Category = 1501 ANd DM.SubCategory <> 1604 Then 'Click and Collect'
When DM.Category = 1502 Then 'Home Delivery'
End 'OrderType',
@vCompanyName as CompanyName,
@vCompanyEmail as CompanyEmail,
@vCompanyURL as CompanyURL,
@vCollectionPointValue as CollectionPointValue,
Case
when @vCheckCollectionPoint = 'True' Then 'Collection Point'
END 'CollectionPoint',
Case
When DM.Category = 1501 ANd DM.SubCategory <> 1604 Then 'Collect'
When DM.Category = 1502 Then 'Despatch'
When DM.Category = 1501 And DM.SubCategory = 1604 Then (Select AppConfigValue from Catalogue.COM.Config_Application
Where AppConfigId='SameDayShippingLabel')
End 'DeliveryMethod',
(Case When DM.Category = 1502 Then 1 Else 0 End) As 'IsHomeDelivery',
(CASE
WHEN DM.Category = 1502
THEN
CAST(
(
SELECT
ISNULL(OA.Name, CustomerName) CustomerName,
OA.AddressLines AddressLines,
OA.City City,
OA.PostalCode PostalCode,
OA.Country Country
FROM COM.Order_Address OA WITH (NoLock)
INNER JOIN COM.DeliveryMethod DM WITH (NoLock) ON DM.pkDeliveryMethodId = tbl.DeliveryMethodId
AND (DM.Category = 1502
OR DM.SubCategory = 1604) --HomeDelivery
AND OA.OrderId = tbl.OrderId
AND OA.AddressType = 'SHIP'
FOR
XML PATH('')
) AS XML
)
ELSE
CAST(
(
SELECT
ISNULL(B.Name, CustomerName) CustomerName,
A.Street AddressLines,
A.City City,
A.PostalCode PostalCode,
A.County Country
FROM Catalogue..Branch B WITH (NoLock)
INNER JOIN Catalogue.COM.[Order] O WITH (NoLock) ON O.CollectStoreId = B.pkBranchID
INNER JOIN Catalogue..[Address] A WITH (NoLock) ON A.fkBranchID = O.CollectStoreId
INNER JOIN COM.DeliveryMethod DM WITH (NoLock) ON DM.pkDeliveryMethodId = tbl.DeliveryMethodId
AND (DM.Category = 1501
OR DM.SubCategory <> 1604) --CC
AND O.pkOrderId = tbl.OrderId
FOR
XML PATH('')
) AS XML
)
END )
AS 'DeliveryAddress'
From COM.DeliveryMethod DM With (NoLock) Where DM.pkDeliveryMethodId = tbl.DeliveryMethodId
For Xml Path('Header'), Type
) As NVarchar(Max)) +
Cast((
Select SKU As 'Line/SKU',
Quantity As 'Line/Quantity',
ProductName As 'Line/ProductName',
Barcode As 'Line/Barcode',
Price As 'Line/Price'
From @tblPrintOrderLines Where OrderNumber = tbl.OrderNumber
For Xml Path('Lines'), Type
) As Varchar(Max)) +
Cast((
Select SUM(Quantity),
OrderNotes OrderNotes,
@vcFooterText As Text
From @tblPrintOrderLines Where OrderNumber = tbl.OrderNumber
For Xml Path('TotalItems'), Type
) As Varchar(Max)) +
'</PackingNote></Document></Documents>'
)
As XML) As 'XML',
@xmlTemplate As XSLT
From @tblOrderData tbl
XML内容：
这是产生的XML内容：
<Documents>
<Document>
<PackingNote>
<Header>
<CustomerName>laura haines</CustomerName>
<OrderDate>15/09/2017 20:38</OrderDate>
<CustomerNumber>000003</CustomerNumber>
<OrderNumber>000024</OrderNumber>
<BatchNumber>1 of 1</BatchNumber>
<OrderType>Click and Collect</OrderType>
<CompanyName>The Retail Suite</CompanyName>
<CompanyURL />
<DeliveryMethod>Collect</DeliveryMethod>
<IsHomeDelivery>0</IsHomeDelivery>
<DeliveryAddress>
<CustomerName>ACC</CustomerName>
<AddressLines>London</AddressLines>
<City>London</City>
</DeliveryAddress>
</Header>
<Lines>
<Line>
<SKU>000044</SKU>
<Quantity>9</Quantity>
<ProductName>Partial Cancel 001</ProductName>
<Price>135.00</Price>
</Line>
</Lines>
<TotalItems>9<Text>Line 1&#xD;Line 2 &#xD;Line 3</Text></TotalItems>
</PackingNote>
</Document>
</Documents>
我如何使用XSL 1.0？
获得线路断路XSLT 1.0：
<fo:table-row>
<fo:table-cell padding-bottom="1mm" padding-left="0.5cm">
<fo:block font-size="10pt" text-align="left">
<xsl:value-of select="text" />
</fo:block>
</fo:table-cell>
</fo:table-row>
当前输出：
Line 1
Line 2 
Line 3
预期输出：
Line 1
Line 2
Line 3
如果您使用的是XSLT 1.0，请替换此行：
<xsl:value-of select="text" />
with：
<xsl:call-template name="replace">
<xsl:with-param name="text" select="text"/>
</xsl:call-template>
并将其添加到样式表中：
<xsl:template name="replace">
<xsl:param name="text"/>
<xsl:param name="search-string" select="'&#xD;'"/>
<xsl:param name="replace-string" select="' '"/>
<xsl:choose>
<xsl:when test="contains($text, $search-string)">
<xsl:value-of select="substring-before($text, $search-string)"/>
<xsl:value-of select="$replace-string"/>
<xsl:call-template name="replace">
<xsl:with-param name="text" select="substring-after($text, $search-string)"/>
</xsl:call-template>
</xsl:when>
<xsl:otherwise>
<xsl:value-of select="$text"/>
</xsl:otherwise>
</xsl:choose>
</xsl:template>
NOTE ：XML对例敏感，在您的XML中，该元素命名为Text而不是text。所以应该是：
<xsl:call-template name="replace">
<xsl:with-param name="text" select="Text"/>
</xsl:call-template>
我如何使用XSL 1.0？
获得线路断路
在查询中，您可以用
或 代替CHAR而不是
或
in XSLT 1.0 ，您可以做与下面相同的事情：
<fo:table-row>
<fo:table-cell padding-bottom="1mm" padding-left="0.5cm">
<fo:block font-size="10pt" text-align="left">
<xsl:value-of select="translate(text,'&#xd;','
')" />
</fo:block>
</fo:table-cell>
</fo:table-row>
XSLT将在线路断裂中转换
或 。请参阅此处的更新转换。
要纠正：
在XSLT 1.0中，可以使用递归模板来替换字符串或xmlns:str="xalan://org.apache.commons.lang.StringUtils"，这需要使用"替换（）"方法。