Yet another SSIS XML Destination

Question

Yet another SSIS XML Destination

beakdan

Old Hand

Points: 393
More actions
May 22, 2017 at 11:24 pm

#325922

Comments posted to this topic are about the item Yet another SSIS XML Destination

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

jason.quackenbush SSC Enthusiast Points: 130 More actions · Answer 1

The article is great and very easy to follow however using writexml will fail if you have an image column, I ran into this just the other day. If you have a workaround for that, could you include it. Thanks

beakdan Old Hand Points: 393 More actions · Answer 2

jason.quackenbush - Tuesday, May 23, 2017 10:53 AM
The article is great and very easy to follow however using writexml will fail if you have an image column, I ran into this just the other day. If you have a workaround for that, could you include it. Thanks

Thank you for taking the time to read it.
I haven't had any issue with binary columns. Are you trying to export a big blob? Could you please tell me the average size of the data in your column to do some tests?
The code is really so simple that I see no workaround for specific cases.

BobMcClellan SSC-Addicted Points: 400 More actions · Answer 3

beakdan,
Thanks so much for taking the time post this awesome article.
It's EXACTLY what I needed! 🙂

I'm actually trying to create the xml file to be used as an RSS Feed.
Would it be possible to insert to additional elements?

I currently have ....
<?xml version="1.0" standalone="yes"?>
<Stories>
<story>
<StoryID>1</StoryID>
<StoryIdea>Morning Workouts</StoryIdea>

And would like to have....
<?xml version="1.0" standalone="yes"?>
<rss version=”2.0”>
<channel>
<Stories>
<story>
<StoryID>1</StoryID>
<StoryIdea>Morning Workouts</StoryIdea>
.....
</channel>
</rss>

Thank in advance and...
Thanks again for posting this article. I was banging my head against the wall trying to get this to work....
Much appreciated!
..bob

beakdan Old Hand Points: 393 More actions · Answer 4

BobMcClellan - Friday, June 8, 2018 2:13 PM
beakdan,
Thanks so much for taking the time post this awesome article.
It's EXACTLY what I needed! 🙂
I'm actually trying to create the xml file to be used as an RSS Feed.
Would it be possible to insert to additional elements?
<rss version=â€2.0â€>
<channel>
I currently have ....
<?xml version="1.0" standalone="yes"?>
<Stories>
<story>
<StoryID>1</StoryID>
<StoryIdea>Morning Workouts</StoryIdea>
And would like to have....
<?xml version="1.0" standalone="yes"?>
<rss version=â€2.0â€>
<channel>
<Stories>
<story>
<StoryID>1</StoryID>
<StoryIdea>Morning Workouts</StoryIdea>
.....
</channel>
</rss>
Thank in advance and...
Thanks again for posting this article. I was banging my head against the wall trying to get this to work....
Much appreciated!
..bob

Bob
It seems that you want to copy the full document generated by the script task into some predefined nodes. You could modify the script task itself or you could post process the XML with an XML task. I think the second option its easier...
So configure an XML task

OperationType: XSLT
Source: The xml produced by the script task
Second Operand: The following XSLT transformation.


<?xml version="1.0" encoding="utf-8"?><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="yes"/>
    <xsl:template match="/">
        <rss version="2.0">
            <channel>
                <xsl:copy>
                    <xsl:apply-templates select="@* | node()"/>
                </xsl:copy>
            </channel>
        </rss>
    </xsl:template>
    <xsl:template match="@* | node()">
        <xsl:copy>
            <xsl:apply-templates select="@* | node()"/>
        </xsl:copy>
    </xsl:template>
</xsl:stylesheet>

Hope it helps
AdÃ¡n Bucio