December 30, 2008 at 7:03 pm
Hi all, I hope there's a simple solution to this, because the complex one is exponentially more involved!
Here's the deal, I have a table of data that is being used as a step in an import process. The data I'm importing exists in Excel and is getting inserted into a SQL table (no sweat.) Some of the fields are then used to create a chunk of XML for storage elsewhere... that's where I run into problems.
Here's some simplified code:
declare @x xml
set @x = ''
select @x.query('
element Practice
{
element Physician
{
element Name
{
element First { sql:column("PhyFName") },
element Middle { sql:column("PhyMName") },
element Last { sql:column("PhyLName") },
element Suffix { sql:column("PhySuffix") }
}
}
}
')
from Forms
Using the above code, four elements (First, Middle, Last, and Suffix) are always created. As in the sample output:
<Practice>
<Physician>
<Name>
<First>Jonathon</First>
<Middle />
<Last>Doe</Last>
<Suffix />
</Name>
</Physician>
</Practice>
As you can see, even though the doctor had no middle name or suffix (they are NULLs in the database), elements were still created for his record.
Does anyone know of a simple way to keep this from happening without having to put tons of if-then statements everywhere?
Thanks!
Jay
December 31, 2008 at 8:15 am
Why do you need to? It's empty.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
December 31, 2008 at 10:01 am
Bob Hovious (12/31/2008)
Why do you need to? It's empty.
Cleanliness. The example I gave was very simplified. The actual data has lots of fields where one of many possiblities will be used. For instance, sometimes we receive actual dates for next appointments, other times we receive only a number (1 month, 3 months, 2 years, etc.) Maybe it's OCD on my part, but I would much rather see my XML built as:
<NextAppt>
<Months>3</Months>
</NextAppt>
Instead of:
<NextAppt>
<Date />
<Days />
<Months>3</Months>
<Years />
</NextAppt>
While we're on the topic, do you think this is the best way to handle that particular situation, or should I do something like:
<NextAppt Type="Months">3</NextAppt>
or
<NextAppt Type="Date">3/15/2009</NextAppt>
I think the latter would be harder to capture in a schema, since the type would change with the value of the attribute... That makes me shudder a bit. But I'm not that advanced with XML in the first place, so this may be an overreaction. Thoughts?
December 31, 2008 at 10:02 am
yup agree i don't see a need too either.. anyway i'm not a guru on xQuery but you can use modify's delete method to remove those empty elements but it still requires you to write a block of codes to check and remove each element.
Personally i would prefer the old school method of if..else.. within the query itself.
If you found a easier solution out do post a reply, i'll be interested to hear.
December 31, 2008 at 10:13 am
Here's my not so elegant working solution:
element Name
{
if ( empty(sql:column("PhyFName") cast as xs:string? ))
then ()
else (element First { sql:column("PhyFName") }),
if ( empty(sql:column("PhyMName") cast as xs:string? ))
then ()
else (element Middle { sql:column("PhyMName") }),
if ( empty(sql:column("PhyLName") cast as xs:string? ))
then ()
else (element Last { sql:column("PhyLName") }),
if ( empty(sql:column("PhySuffix") cast as xs:string? ))
then ()
else (element Suffix { sql:column("PhySuffix") })
}
December 31, 2008 at 10:33 am
Thanks for posting your solution, nv know when your post would be helpfull.
personally, i think you can skip the casting
if (empty(sql:column("PhySuffix")))
the other way that i mentioned .modify(delete,...) is also wordy..
so long as it does the job without much performance issues is a gd solution
December 31, 2008 at 10:49 am
Hey Jason,
After Matt's comment in your other post, I realized that there was a much better way.... using SELECT/FOR XML. The query at the bottom produced the output format you wanted very simply.
------------------------
declare @x xml
declare @forms Table (PhyFName varchar(20), PhyMName varchar(20), PhyLName varchar(20), PhySuffix varchar(5), ThisDate datetime)
set @x = ''
insert into @forms
select 'John',null,'Jones','',GETDATE()
select * from @forms
select PhyFname as [First], PhyMname as [Middle], PhyLName as [Last], convert(varchar(16),ThisDate,101) as [Date]
from @forms
FOR XML PATH ('Physician'),ROOT('Practice')
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
December 31, 2008 at 10:58 am
Sorry - I just noticed how little info I actually provided on the other thread...:)
Thanks for the assist.
In case you need any of those as attibutes - just throw an @ on the front of the column alias....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 31, 2008 at 12:24 pm
Bob Hovious (12/31/2008)
Hey Jason,After Matt's comment in your other post, I realized that there was a much better way.... using SELECT/FOR XML. The query at the bottom produced the output format you wanted very simply.
------------------------
declare @x xml
declare @forms Table (PhyFName varchar(20), PhyMName varchar(20), PhyLName varchar(20), PhySuffix varchar(5), ThisDate datetime)
set @x = ''
insert into @forms
select 'John',null,'Jones','',GETDATE()
select * from @forms
select PhyFname as [First], PhyMname as [Middle], PhyLName as [Last], convert(varchar(16),ThisDate,101) as [Date]
from @forms
FOR XML PATH ('Physician'),ROOT('Practice')
BIG THANKS to Matt and Bob for tag teaming this one for me. As I mentioned, I'm no expert at XML in SS and I didn't even know about the FOR XML PATH directive in SQL 2005. All the SQLXML code I've seen here was written by a previous employee in SQL 2000 and was such a PITA to read and maintain. Now I see that you can create fairly complex XML without the use of all sorts of unions, tags, and parents... which means I have made up my mind on which way to go!
Thanks again!
December 31, 2008 at 12:48 pm
You're welcome, Jason, and Happy New Year. 😀
While you are doing your homework on FOR XML, be sure to look up nested queries in conjunction with PATH. You may need them later on. It's an easier way to do most of the things you used to have to do with EXPLICIT.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
December 31, 2008 at 12:51 pm
Bob Hovious (12/31/2008)
You're welcome, Jason, and Happy New Year. 😀While you are doing your homework on FOR XML, be sure to look up nested queries in conjunction with PATH. You may need them later on. It's an easier way to do most of the things you used to have to do with EXPLICIT.
A little hint for the road on the sub-queries, be sure to add the TYPE directive to your PATH statement in the sub-queries. If you don't you will get some god-awful confusing message about invalid XML, etc.......
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 31, 2008 at 1:54 pm
Bob Hovious (12/31/2008)
You're welcome, Jason, and Happy New Year. 😀While you are doing your homework on FOR XML, be sure to look up nested queries in conjunction with PATH. You may need them later on. It's an easier way to do most of the things you used to have to do with EXPLICIT.
Yes, I came accross them in my research. They seem very helpful. Speaking of which, for anyone searching for more information on the topics discussed in this thread, there's an excellent article about the great enhancements to SQL 2005's 'FOR XML' functionality here:
Microsoft MSDN - What's New in FOR XML in Microsoft SQL Server 2005
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply