www.usoft.com To www.usoft.com
Question

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

  • 10 February 2022
  • 2 replies
  • 71 views
Gert.K
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

R
Userlevel 2
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

R
Userlevel 2
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


Terms & ConditionsCookie settings