x

Exclude XML nodes based on a dataset

Here is the question, in brief it is about excluding / returning several nodes, depending on whether values are found in a reference table or not.

I have a SQL Server table with XML datatype, which contains XML similar to this:

<header>

<id>123</id>

<timestamp>2012-11-14 00:00</timestamp>

</header>

<product>

<color>red</color>

<model>11111</model>

</product>


<product>

<color>white</color>

<model>22222</model>

</product>

<product>

<color>green</color>

<model>333333</model>

</product>

Then I have SelectedColors table which contains a list of colors, let's say

White

Blue

What I would like to do is to parse the XML and match for each XML file in the database to match the colors in the SelectedColors table and output a XML file to disk which contains only the nodes which have the nodes matching the SelectedColors.

SSIS would be used or any other method would be good as well.

The output should be XML file saved to disk, which should look like this:

<header>

<id>123</id>

<timestamp>2012-11-14 00:00</timestamp>

</header>


<product>

<color>white</color>

<model>22222</model>

</product>
more ▼

asked Nov 14, 2012 at 08:25 PM in Default

sibir1us gravatar image

sibir1us
0 1 1 1

(comments are locked)
10|1200 characters needed characters left

0 answers: sort voted first
Be the first one to answer this question
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x147
x8

asked: Nov 14, 2012 at 08:25 PM

Seen: 721 times

Last Updated: Nov 14, 2012 at 08:25 PM