RE: MS SQL XML Datatype
Yep. You can actually store a DTD in the SQL database that will validate any XML inserted into it if you wish. I believe you can also build very fast indices over an XML field, where a text field w/ full text index may not be as zippy. Chris Peterson Gainey IT Adobe Certified Advanced Coldfusion Developer -Original Message- From: Chad Gray Sent: Thursday, June 28, 2007 2:52 PM To: CF-Talk Subject: MS SQL XML Datatype So is there any advantage or reason I should be storing XML data in a XML field vs. varchar?? ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 MX7 integration create powerful cross-platform RIAs http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJQ Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:282495 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: MS SQL XML Datatype
So is there any advantage or reason I should be storing XML data in a XML field vs. varchar?? Yes, the same reason you use other specific datatypes in SQL. The database can validate your data, for example. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! This email has been processed by SmoothZap - www.smoothwall.net ~| Create Web Applications With ColdFusion MX7 Flex 2. Build powerful, scalable RIAs. Free Trial http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJS Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:282496 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: MS SQL XML Datatype
Call me crazy, but I've just never liked the idea of storing XML in the database in any form. The first form of normalization states that each column should hold an atomic value and XML is anything but that. Maybe I'm old-school, but if I wanted to store a set of complex, nested data; I would use a series of tables with separate columns for each piece. If I was forced to handle XML, then that seems like work for a higher tier like a CF or Java application. At my job we do use XML to communicate between our internal apps (CF, ASP, Java, PowerBuilder), our customer's external apps, and even to pass large amounts of data to stored procs, but all that happens at the application level. We don't store XML in the database ever. I like Microsoft's ingenuity there, but it just doesn't sit right with me. Am I just stuck in the mud, or is this really one of those things that SQL server CAN do, but shouldn't necessarily do? ~Brad -Original Message- From: Dave Watts [mailto:[EMAIL PROTECTED] Sent: Thursday, June 28, 2007 2:16 PM To: CF-Talk Subject: RE: MS SQL XML Datatype So is there any advantage or reason I should be storing XML data in a XML field vs. varchar?? Yes, the same reason you use other specific datatypes in SQL. The database can validate your data, for example. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ ~| ColdFusion MX7 by Adobe® Dyncamically transform webcontent into Adobe PDF with new ColdFusion MX7. Free Trial. http://www.adobe.com/products/coldfusion?sdid=RVJV Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:282499 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: MS SQL XML Datatype
Brad, I used XML to store an un-known amount of data coming from the form scope. I wanted to, in my debug template, store any variable coming in to a page via form, url, etc. I made my primary DB columns things like IP, requested page, date time, etc. Then I made an XML column called 'variables' and stuck all my user variables into that. Just one use where XML makes sense =) Chris Peterson Gainey IT Adobe Certified Advanced Coldfusion Developer -Original Message- From: Brad Wood [mailto:[EMAIL PROTECTED] Sent: Thursday, June 28, 2007 3:31 PM To: CF-Talk Subject: RE: MS SQL XML Datatype Call me crazy, but I've just never liked the idea of storing XML in the database in any form. The first form of normalization states that each column should hold an atomic value and XML is anything but that. Maybe I'm old-school, but if I wanted to store a set of complex, nested data; I would use a series of tables with separate columns for each piece. If I was forced to handle XML, then that seems like work for a higher tier like a CF or Java application. At my job we do use XML to communicate between our internal apps (CF, ASP, Java, PowerBuilder), our customer's external apps, and even to pass large amounts of data to stored procs, but all that happens at the application level. We don't store XML in the database ever. I like Microsoft's ingenuity there, but it just doesn't sit right with me. Am I just stuck in the mud, or is this really one of those things that SQL server CAN do, but shouldn't necessarily do? ~Brad ~| ColdFusion MX7 by Adobe® Dyncamically transform webcontent into Adobe PDF with new ColdFusion MX7. Free Trial. http://www.adobe.com/products/coldfusion?sdid=RVJV Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:282503 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: MS SQL XML Datatype
Yeah, I see what you are saying. You could have easily had a separate attributes table with name and value columns and a record per variable, but that could definitely seem like overkill when you basically just want to stick the equivalent of a core dump somewhere for later troubleshooting. Don't think I was dogging on you for storing XML or anything, the thread just triggered something I've thought/wondered a lot about and I wanted to see what other people's take on it was. ;- Come to think of it, we have been known to wddx form and url structs for storage in our DB error logging too. ~Brad -Original Message- From: Peterson, Chris [mailto:[EMAIL PROTECTED] Sent: Thursday, June 28, 2007 3:01 PM To: CF-Talk Subject: RE: MS SQL XML Datatype Brad, I used XML to store an un-known amount of data coming from the form scope. I wanted to, in my debug template, store any variable coming in to a page via form, url, etc. I made my primary DB columns things like IP, requested page, date time, etc. Then I made an XML column called 'variables' and stuck all my user variables into that. Just one use where XML makes sense =) Chris Peterson Gainey IT Adobe Certified Advanced Coldfusion Developer -Original Message- From: Brad Wood [mailto:[EMAIL PROTECTED] Sent: Thursday, June 28, 2007 3:31 PM To: CF-Talk Subject: RE: MS SQL XML Datatype Call me crazy, but I've just never liked the idea of storing XML in the database in any form. The first form of normalization states that each column should hold an atomic value and XML is anything but that. Maybe I'm old-school, but if I wanted to store a set of complex, nested data; I would use a series of tables with separate columns for each piece. If I was forced to handle XML, then that seems like work for a higher tier like a CF or Java application. At my job we do use XML to communicate between our internal apps (CF, ASP, Java, PowerBuilder), our customer's external apps, and even to pass large amounts of data to stored procs, but all that happens at the application level. We don't store XML in the database ever. I like Microsoft's ingenuity there, but it just doesn't sit right with me. Am I just stuck in the mud, or is this really one of those things that SQL server CAN do, but shouldn't necessarily do? ~Brad ~| ColdFusion 8 beta â Build next generation applications today. Free beta download on Labs http://www.adobe.com/cfusion/entitlement/index.cfm?e=labs_adobecf8_beta Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:282506 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: MS SQL XML Datatype
Call me crazy, but I've just never liked the idea of storing XML in the database in any form. I generally agree with you, but just like almost anything else, you should never say never. There are occasional cases where you want to store denormalized data, and sometimes that data may be XML. For example, if I wanted to store LiveCycle Designer forms in a database - the forms themselves, rather than their visual PDF representation - I'd store the raw XFA content (which is XML) rather than trying to represent them with a normalized data structure. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! This email has been processed by SmoothZap - www.smoothwall.net ~| ColdFusion MX7 and Flex 2 Build sales marketing dashboard RIAâs for your business. Upgrade now http://www.adobe.com/products/coldfusion/flex2?sdid=RVJT Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:282526 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
