Home » Category » Microsoft SQL Server

Microsoft SQL Server: Zeroing NULL or NonExistent Values in Tabular XML Data?

204| Fri, 30 May 2008 12:50:00 GMT| jeevesdeveyra| Comments (7)
Hi,
I have the following XML snippet

<PieceofData Name="Piece1">
<Place Day="Monday" Value="5">
<Place Day="Tuesday" Value="4">
<Place Day="Wednesday" Value="3">
</PieceofData>
<PieceofData Name="Piece2">
<Place Day="Monday" Value="5">
<Place Day="Wednesday" Value="1">
</PieceofData>
<PieceofData Name="Piece3">
<Place Day="Wednesday" Value="6">
</PieceofData>

Target output:
--M-- T--W--
Piece1 : 5 : 4 : 3 :
Piece2 : 5 : 0 : 1 :
Piece3 : 0 : 0 : 6 :

I Can't seem to find anything on ignoring or zeroing "BLANK" elements. Anyway to do this?

I was thinking about doing CROSS JOINS before generating the XML but it might end up wasting too much system resources once I generate too many "PieceOfData"s

Thanks,
Jeeves

Keywords & Tags: zeroing, null, nonexistent, values, tabular, xml, data, microsoft sql server

URL: http://database.itags.org/sql-server/382137/
 
«« Prev - Next »» 7 helpful answers below.
If your XML would look like:

<Piece>
<Monday>5</Monday>
<Tuesday>4</Tuesday>
<Wednesday>3</Wednesday>
</Piece>

Then you could easily shred it into a table where missing days would map to
a NULL value using either the mapping schemas or OpenXML.

Can you change your XML format (or preprocess it using XSLT?)?

Thanks
Michael

"Jeeves De Veyra" <me...jeevester.com> wrote in message
news:enheLRMrEHA.1272...TK2MSFTNGP09.phx.gbl...
> Hi,
> I have the following XML snippet
> <PieceofData Name="Piece1">
> <Place Day="Monday" Value="5">
> <Place Day="Tuesday" Value="4">
> <Place Day="Wednesday" Value="3">
> </PieceofData>
> <PieceofData Name="Piece2">
> <Place Day="Monday" Value="5">
> <Place Day="Wednesday" Value="1">
> </PieceofData>
> <PieceofData Name="Piece3">
> <Place Day="Wednesday" Value="6">
> </PieceofData>
> Target output:
> --M-- T--W--
> Piece1 : 5 : 4 : 3 :
> Piece2 : 5 : 0 : 1 :
> Piece3 : 0 : 0 : 6 :
> --
> I Can't seem to find anything on ignoring or zeroing "BLANK" elements. Any
> way to do this?
> I was thinking about doing CROSS JOINS before generating the XML but it
> might end up wasting too much system resources once I generate too many
> "PieceOfData"s
> Thanks,
> Jeeves
>

michaelrysmsft | Fri, 30 May 2008 12:51:00 GMT |

Thanks Michael.

I consider myself a newbie on XML but will check it out. The only
complication I see is that the "Day" attribute is not really fixed... for
example instead of days of the week it may be dates in MM/DD/YYYY format.
Which can lead to a lot of combinations that may not be mappable.

Or am i misreading what you wrote?

Jeeves

"Michael Rys [MSFT]" <mrys...online.microsoft.com> wrote in message
news:OcKn%23MNrEHA.3428...TK2MSFTNGP11.phx.gbl...
> If your XML would look like:
> <Piece>
> <Monday>5</Monday>
> <Tuesday>4</Tuesday>
> <Wednesday>3</Wednesday>
> </Piece>
> Then you could easily shred it into a table where missing days would map
to[vbcol=seagreen]
> a NULL value using either the mapping schemas or OpenXML.
> Can you change your XML format (or preprocess it using XSLT?)?
> Thanks
> Michael
> "Jeeves De Veyra" <me...jeevester.com> wrote in message
> news:enheLRMrEHA.1272...TK2MSFTNGP09.phx.gbl...
Any
>

jeevesdeveyra | Fri, 30 May 2008 12:52:00 GMT |

date formats are probably not easily mappable into column names. In that
case your relational schema and the XML should name the property/column a
generic name (such as day) and have a column value that indicates the date.

You can see some examples with OpenXML in the Books Online documentation.

Best regards
Michael

"Jeeves De Veyra" <me...jeevester.com> wrote in message
news:%23h8SoqVrEHA.3324...TK2MSFTNGP15.phx.gbl...
> Thanks Michael.
> I consider myself a newbie on XML but will check it out. The only
> complication I see is that the "Day" attribute is not really fixed... for
> example instead of days of the week it may be dates in MM/DD/YYYY format.
> Which can lead to a lot of combinations that may not be mappable.
> Or am i misreading what you wrote?
> Jeeves
> "Michael Rys [MSFT]" <mrys...online.microsoft.com> wrote in message
> news:OcKn%23MNrEHA.3428...TK2MSFTNGP11.phx.gbl...
> to
> Any
>

michaelrysmsft | Fri, 30 May 2008 12:53:00 GMT |

So is there an answer for the "zeroing NULL or NonExistent Values" question?
I have a similiar issue where I want the fields to appear as attributes
whether they are null or not (e.g. if 'Name' is null, then the attribute
would be Name="").

"Michael Rys [MSFT]" wrote:

> date formats are probably not easily mappable into column names. In that
> case your relational schema and the XML should name the property/column a
> generic name (such as day) and have a column value that indicates the date.
> You can see some examples with OpenXML in the Books Online documentation.
> Best regards
> Michael
> "Jeeves De Veyra" <me...jeevester.com> wrote in message
> news:%23h8SoqVrEHA.3324...TK2MSFTNGP15.phx.gbl...
>
>

chris | Fri, 30 May 2008 12:54:00 GMT |

Use ISNULL in the select clause.

HTH
Michael

"chris" <chris...discussions.microsoft.com> wrote in message
news:45609A01-3ED2-4328-99BC-2E084FDA6193...microsoft.com...[vbcol=seagreen]
> So is there an answer for the "zeroing NULL or NonExistent Values"
> question?
> I have a similiar issue where I want the fields to appear as attributes
> whether they are null or not (e.g. if 'Name' is null, then the attribute
> would be Name="").
>
> "Michael Rys [MSFT]" wrote:

michaelrysmsft | Fri, 30 May 2008 12:55:00 GMT |

Thanks for the reply.

I have been using COALESCE (similiar to ISNULL) but was wondering more about
either an sp_... config setting, or some FOR XML parameter that I was unaware
of.

Could you comment on the performance impact (if any) about using such
functions in the stored proc?

"Michael Rys [MSFT]" wrote:

> Use ISNULL in the select clause.
> HTH
> Michael
> "chris" <chris...discussions.microsoft.com> wrote in message
> news:45609A01-3ED2-4328-99BC-2E084FDA6193...microsoft.com...
>
>

chris | Fri, 30 May 2008 12:56:00 GMT |

They will impact the performance, but I would assume that their impact is
within an acceptable range (best is to try it out).

In SQL Server 2005, you can use more complex XQuery expressions with the
nodes() and value() methods. Although I am not sure that that would be more
efficient...
Best regards
Michael

"chris" <chris...discussions.microsoft.com> wrote in message
news:9765AD39-2A01-43AB-9EED-2FA2F530AE69...microsoft.com...[vbcol=seagreen]
> Thanks for the reply.
> I have been using COALESCE (similiar to ISNULL) but was wondering more
> about
> either an sp_... config setting, or some FOR XML parameter that I was
> unaware
> of.
> Could you comment on the performance impact (if any) about using such
> functions in the stored proc?
> "Michael Rys [MSFT]" wrote:

michaelrysmsft | Fri, 30 May 2008 12:57:00 GMT |

Microsoft SQL Server Hot Answers

Microsoft SQL Server New questions

Microsoft SQL Server Related Categories