Question

How to use values in a xml as a list in a sql statement

  • 10 February 2022
  • 2 replies
  • 25 views

Userlevel 1

I have a relative simple xml as input

<annotations>
<annotation on_class="CustomREST" name="AuthSaml" method="entity" value=""/>
<annotation on_class="CustomREST" name="AuthSaml" method="value" value="a"/>
<annotation on_class="DefaultJobREST" name="AuthSaml" method="entity" value=""/>
<annotation on_class="DefaultJobREST" name="AuthSaml" method="value" value="b"/>
</annotations>

I want to use the values from the annotation where the method equals value. I have a small xsl transformation to grab the values, but with this I only get one value, so I cant process it as 2 values.

invoke usxsl.apply with select
'<annotations> <anno.... </annotations>',

'<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="text" indent="no" omit-xml-declaration="yes" />
<xsl:template match="annotation[@method=''value'']">
<xsl:value-of select="@value"/>
</xsl:template>
</xsl:stylesheet>'


/*which results in*/
-----report on script no. 1
a b
-----end of report on script no. 1

What I want to use it for is something like this

Invoke MyComponent.Function with select
TAB1.COL1, TAB1.COL2
from
TAB1
where
TAB1.ID IN (<xslapplyresult>)

How do I achieve this?


2 replies

Badge +2

Hi Gert,

Adding <xsl:for-each> might give you a starting point:

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

<xsl:template match="/">
<xsl:for-each select="annotations/annotation">
<xsl:value-of select="@value"/>
<xsl:text>, </xsl:text>
</xsl:for-each>
</xsl:template>

</xsl:stylesheet>

Cheers,

 

Robert

P.S. you might like to add quotes to the result

Badge +2

Gert,

Here’s a  similar example used to transfer the output of xml.sqlexport into json:
 

<?xml version="1.0" encoding="windows-1252"?>
<!-- Header -->
<xsl:stylesheet
version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
>
<xsl:output method="text" encoding="UTF-8" />

<xsl:template match="Expression">
</xsl:template>

<xsl:template match="Row">
<xsl:text>&#10;&#9;{</xsl:text>
<xsl:for-each select="Field">
<xsl:text>&#10;&#9;&#9;"</xsl:text>
<xsl:value-of select="@alias"/>
<xsl:text>"</xsl:text>
<xsl:text>:</xsl:text>
<xsl:text>"</xsl:text>
<xsl:value-of select="."/>
<xsl:text>"</xsl:text>
<xsl:if test="position()&lt;last()"><xsl:text>,</xsl:text></xsl:if>
<xsl:text>&#10;</xsl:text>
</xsl:for-each>
</xsl:template>

</xsl:stylesheet>

And the same solution using templates:

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

<xsl:template match="/">
<xsl:apply-templates />
</xsl:template>

<xsl:template match="annotations">
<xsl:text>( </xsl:text>
<xsl:apply-templates select="annotation"/>
<xsl:text> )</xsl:text>
</xsl:template>

<xsl:template match="annotation">
<xsl:value-of select="@value"/>
<xsl:if test="position()&lt;last()">
<xsl:text>, </xsl:text>
</xsl:if>
</xsl:template>

</xsl:stylesheet>

I like the xsl:if in particular ;-) (big thanks to Frank)

 

Reply