0

I need to produce XML Document from Universe Database. These XML files are the source for SSIS Packages. So firstly I have to produce XML Files. For that I am using following command. SELECT FIRST 1000 LIST TOXML ELEMENTS It is returning 1000 Records, Its fine. I have some questions here, 1. How I can specify a condition to select only satisfied records a. Example where name like “S%” (SQL Server) 2. Some fields have multi values like 

<RECORD>
<BRANCH>A</BRANCH>
<SUB_BRANCH>A1</SUBBRANCH>
<SUB_BRANCH>A2</SUBBRANCH>
<SUB_BRANCH>A3</SUBBRANCH>
</RECORD>
<RECORD>
<BRANCH>B</BRANCH>
<SUB_BRANCH>B1</SUBBRANCH>
<SUB_BRANCH>B2</SUBBRANCH>
</RECORD>
<RECORD>
<BRANCH>C</BRANCH>
<SUB_BRANCH>C1</SUBBRANCH>
</RECORD>

From this I want to make separate records based on

Like below :

<RECORD>
<BRANCH>A</BRANCH>
<SUB_BRANCH>A1</SUBBRANCH>
</RECORD>
<BRANCH>A</BRANCH>
<SUB_BRANCH>A2</SUBBRANCH>
</RECORD>
<BRANCH>A</BRANCH>
<SUB_BRANCH>A3</SUBBRANCH>
</RECORD>
<RECORD>
<BRANCH>B</BRANCH>
<SUB_BRANCH>B1</SUBBRANCH>
</RECORD>
<BRANCH>B</BRANCH>
<SUB_BRANCH>B2</SUBBRANCH>
</RECORD>
<RECORD>
<BRANCH>C</BRANCH>
<SUB_BRANCH>C1</SUBBRANCH>
</RECORD>

Is this possible?

Thanks,

ram.bi
  • 283
  • 4
  • 15

2 Answers2

0

I don't understand the selection thing, so just commenting on the XSL part here. There are a few issues with your source XML when it comes to processing. First you need a root element to be able to process it. Second, your SUB_BRANCH appear to close with SUBBRANCH (without the underscore) so your XML is not valid. Assuming those are fixed:

<xsl:template match="RECORD">
    <xsl:element name="RECORD">
    <xsl:for-each select=".//SUB_BRANCH">
        <xsl:element name="BRANCH">
            <xsl:value-of select="../*"/>
        </xsl:element>
        <xsl:element name="SUB_BRANCH">
            <xsl:value-of select="node()"/>
       </xsl:element>

    </xsl:for-each>
        </xsl:element>
</xsl:template>

would be one way of giving you what you wanted at the output but I stress, it would only work if you fix your source data (and if that is automatically generated, whatever generated it)

Woody
  • 5,052
  • 2
  • 22
  • 28
0

Your SELECT could look like this

SELECT filename WITH fieldname LIKE ...something... AND WHEN mvfieldname = somethingelse

The WITH clause will take care of the single value fields and the WHEN clause of subvalues in multi-valued fields

user1154397
  • 1
  • 1