Добрый день уважаемые коллеги
Мне нужно сформировать XSLT вывод - текст, в котором будет XML:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
BEGIN TRANSACTION
INSERT INTO PostScripts(ProgramID, [Script])
VALUES( 1 ,
'
DECLARE @x XML
SELECT @x = [Definition] FROM Definitions WHERE ProgramID = 1
SELECT @x = CAST(@x AS NVARCHAR(MAX))+CAST(''<Parameter ID="P1" Interpretator="I1" Value=""/>
'' AS NVARCHAR(1024))
SET @x.modify(''insert /Parameter into (/Program/Parameters)[1]'')
SET @x.modify(''delete /Parameter'');
UPDATE Definitions SET [Definition] = @x WHERE ProgramID = 1'
)
INSERT INTO PostScripts(ProgramID, [Script])
VALUES( 1 ,
'
DECLARE @x XML
SELECT @x = [Definition] FROM Definitions WHERE ProgramID = 1
SELECT @x = CAST(@x AS NVARCHAR(MAX))+CAST(''<Parameter ID="P2" Interpretator="I2" Value=""/>
'' AS NVARCHAR(1024))
SET @x.modify(''insert /Parameter into (/Program/Parameters)[1]'')
SET @x.modify(''delete /Parameter'');
UPDATE Definitions SET [Definition] = @x WHERE ProgramID = 1'
)
COMMIT TRANSACTION
на основании XML:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
<Program ID="1">
<ExternalParameters>
<Parameter ID="P1" Interpretator="I1" Value=""/>
<Parameter ID="P2" Interpretator="I2" Value=""/>
</ExternalParameters>
<Process>
<Creation>
<ExternalParameter>P1</ExternalParameter>
<ExternalParameter>P2</ExternalParameter>
</Creation>
</Process>
</Program>
Сейчас трансформация формируется на основании шаблона:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:msxsl="urn:schemas-microsoft-com:xslt" exclude-result-prefixes="msxsl"
>
<xsl:output method="xml" indent="no" omit-xml-declaration="yes"/>
<xsl:template match="Program">
BEGIN TRANSACTION
<xsl:for-each select="Process/Creation/ExternalParameter">
<xsl:variable name="ParameterName">
<xsl:value-of select="text()"/>
</xsl:variable>
INSERT INTO PostScripts(ProgramID, [Script])
VALUES(<xsl:value-of select="/Program/@ID"/>, <xsl:call-template name="sql-text">
<xsl:with-param name="Text">
DECLARE @x XML
SELECT @x = [Definition] FROM Definitions WHERE ProgramID = <xsl:value-of select="/Program/@ID"/>
SELECT @x = CAST(@x AS NVARCHAR(MAX))+CAST('
<xsl:copy-of select="/Program/ExternalParameters/Parameter[@ID=$ParameterName]"/>' AS NVARCHAR( 1024 ))
SET @x.modify('insert /Parameter into (/Program/Parameters)[1]')
SET @x.modify('delete /Parameter');
UPDATE Definitions SET [Definition] = @x WHERE ProgramID = <xsl:value-of select="/Program/@ID"/>
</xsl:with-param>
</xsl:call-template>)
</xsl:for-each>
COMMIT TRANSACTION
</xsl:template>
<xsl:template name="sql-text">
<!-- ищет апостроф и заменяет его на два апострофа-->
<xsl:param name="Text"/>
<xsl:variable name="Apos">
<xsl:text disable-output-escaping="yes">'</xsl:text>
</xsl:variable>
<xsl:variable name="Apos2">
<xsl:text disable-output-escaping="yes">''</xsl:text>
</xsl:variable>
'<xsl:call-template name="replace-string">
<xsl:with-param name="text">
<xsl:copy-of select="$Text"/>
</xsl:with-param>
<xsl:with-param name="from" select="$Apos"/>
<xsl:with-param name="to" select="$Apos2"/>
</xsl:call-template>'
</xsl:template>
<xsl:template name="replace-string" match="text()" mode="replace">
<!-- заменяет в строке text все строки from на строки to-->
<xsl:param name="text" select="."/>
<xsl:param name="from"/>
<xsl:param name="to"/>
<xsl:choose>
<xsl:when test="contains($text, $from)">
<xsl:value-of select="substring-before($text, $from)"/>
<xsl:copy-of select="$to"/>
<xsl:call-template name="replace-string">
<xsl:with-param name="text" select="substring-after($text, $from)"/>
<xsl:with-param name="from" select="$from"/>
<xsl:with-param name="to" select="$to"/>
</xsl:call-template>
</xsl:when>
<xsl:otherwise>
<xsl:value-of select="$text"/>
</xsl:otherwise>
</xsl:choose>
</xsl:template>
</xsl:stylesheet>
и дает следующий результат
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
BEGIN TRANSACTION
INSERT INTO PostScripts(ProgramID, [Script])
VALUES( 1 ,
'
DECLARE @x XML
SELECT @x = [Definition] FROM Definitions WHERE ProgramID = 1
SELECT @x = CAST(@x AS NVARCHAR(MAX))+CAST(''
'' AS NVARCHAR(1024))
SET @x.modify(''insert /Parameter into (/Program/Parameters)[1]'')
SET @x.modify(''delete /Parameter'');
UPDATE Definitions SET [Definition] = @x WHERE ProgramID = 1'
)
INSERT INTO PostScripts(ProgramID, [Script])
VALUES( 1 ,
'
DECLARE @x XML
SELECT @x = [Definition] FROM Definitions WHERE ProgramID = 1
SELECT @x = CAST(@x AS NVARCHAR(MAX))+CAST(''
'' AS NVARCHAR(1024))
SET @x.modify(''insert /Parameter into (/Program/Parameters)[1]'')
SET @x.modify(''delete /Parameter'');
UPDATE Definitions SET [Definition] = @x WHERE ProgramID = 1'
)
COMMIT TRANSACTION
получается, что при обработке контекста шаблоном sql-text весь XML в тексте преобразуется в текст, а его надо оставить, не подскажите как это можно сделать?
