Omit null columns in XML output
Posted: Fri Mar 01, 2013 10:07 am
I'm not too XML savvy, and I'm modeling this server job on existing jobs. And I apologize that my terminology may be imperfect, too.
We are mapping multiple columns to individual output rows with tags assigned to each column value like this:
<TransactionHistory itemId="205772" channelId="00002" startDate="2011-01-05" endDate="2011-01-11" transType="Addition" x="5" y="45" catType="MULTI" subcatType="00012"/>
<TransactionHistory itemId="205772" channelId="00002" startDate="2011-01-05" endDate="2011-01-11" transType="Addition" x="" y="" catType="MULTI" subcatType="00012"/>
I'm trying to figure out how to omit the x and y columns/tags when x="" and y="". So it should look like this:
<TransactionHistory itemId="205772" channelId="00002" startDate="2011-01-05" endDate="2011-01-11" transType="Addition" x="5" y="45" catType="MULTI" subcatType="00012"/>
<TransactionHistory itemId="205772" channelId="00002" startDate="2011-01-05" endDate="2011-01-11" transType="Addition" catType="MULTI" subcatType="00012"/>
x and y are defined as varchar nullable=N, although setting them to nullable=Y doesn't seem to make any difference.
I've mapped @NULL to the x and y columns in the transformer just before the XML stage to create the output file. However I keep getting x="" y="" in the output file, and I only want them when there is a value in the column.
Am I missing something? It's probably something really obvious.... and simple....
Would appreciate some assistance.
Thanks, Lynda
We are mapping multiple columns to individual output rows with tags assigned to each column value like this:
<TransactionHistory itemId="205772" channelId="00002" startDate="2011-01-05" endDate="2011-01-11" transType="Addition" x="5" y="45" catType="MULTI" subcatType="00012"/>
<TransactionHistory itemId="205772" channelId="00002" startDate="2011-01-05" endDate="2011-01-11" transType="Addition" x="" y="" catType="MULTI" subcatType="00012"/>
I'm trying to figure out how to omit the x and y columns/tags when x="" and y="". So it should look like this:
<TransactionHistory itemId="205772" channelId="00002" startDate="2011-01-05" endDate="2011-01-11" transType="Addition" x="5" y="45" catType="MULTI" subcatType="00012"/>
<TransactionHistory itemId="205772" channelId="00002" startDate="2011-01-05" endDate="2011-01-11" transType="Addition" catType="MULTI" subcatType="00012"/>
x and y are defined as varchar nullable=N, although setting them to nullable=Y doesn't seem to make any difference.
I've mapped @NULL to the x and y columns in the transformer just before the XML stage to create the output file. However I keep getting x="" y="" in the output file, and I only want them when there is a value in the column.
Am I missing something? It's probably something really obvious.... and simple....
Would appreciate some assistance.
Thanks, Lynda