xml
who'd want to such a crazy thing? well me. i just wanted to find the dirtiest quickest way with zero overhead to push data directly into a sharepoint list. i found a few articles with some information about this, but the closest one was this one using jquery. so here it is in vbscript with no extra libraries or anything (except what is build into windows which is still plenty). one cool thing about this is it was quite faster than the .net method i posted previously. i attribute that to building a nice web reference with all the different...
As many of my colleagues know, I am not a really big fan of XML. I think it's bloated. I think it's not needed. I don't think it accomplishes much if anything that couldn't be done before just by sending text delimited in a different way. I'm not sure why if I want to tell someone a yes or a no answer on something being found, I have to wrap it in 1000 characters of SOAP data when i could just send a 0 a 1 or nothing and accomplish the same thing. Or why if I want a list...
SERIOUSLY too long of an article name, but hey, it covers a large amount of stuff. I basically had some large XML blobs (wordml docs) I needed to store in SQL and I wanted to store them as compressed BLOBs to save space. These are the articles I found most useful at the time when I was working on that.
Using CLR integration to compress BLOBs/CLOBs in SQL Server 2005
CLR Stored Procedures
Passing a DataSet to a SQLCLR Stored Procedure
Storing Binary Files Directly in the Database Using ASP.NET 2.0
How to read and write a file to or from a BLOB column by...
This is in response to this article and is me giving dennis a hard time. =P
byte[] XmlToByte(XmlDocument d){ using (StringWriter sw = new StringWriter()) { using (XmlTextWriter xw = new XmlTextWriter(sw)) { d.WriteTo(xw); ASCIIEncoding encoding = new ASCIIEncoding(); return encoding.GetBytes(sw.ToString()); } }}
-- create a mini inventory structure-- let's say it has toys in itdeclare @x xmlset @x ='<items> <item id="1" type="toy"> <name>car</name> <description>toy car</description> <price>10</price> </item> <item id="2" type="toy"> <name>bike</name> <description>toy bike</description> <price>100</price> </item> <item id="3" type="sport"> <name>bike</name> <description>real bike</description> <price>100</price> </item></items>'--look at the toysselect x.item.value('@id[1]','int') [id] , x.item.value('@type[1]','varchar(20)') [type] , x.item.value('name[1]', 'varchar(20)') [name] , x.item.value('description[1]', 'varchar(20)') [description] , x.item.value('price[1]', 'money') [price]from @x.nodes('//items/item') as x(item)--wait, they should all be toys.--what's that real bike doing in here?--let's delete the non toysset @x.modify('delete (/items/item[@type!="toy"])')--hmm, that price is still way wrong on our toy bike, let's fix itset @x.modify( 'replace value of (/items/item[description/text() = "toy bike"]/price/text())[1] with "10"')--yay!select x.item.value('name[1]', 'varchar(20)') [name] , x.item.value('price[1]', 'money') [price]from @x.nodes('//items/item') as x(item)
passing csv to a stored proc is bad m'kay. it leaves you open to sql
injection attacks and in general isn't the best practice anyway. i'm
going to avoid entire code samples just because it's too much typing,
but let's take a sample proc and assume you are passing in a
varchar(1000) (@csv) comma delimited string. you could do something
like this:declare sql varchar(2000)set sql = 'select * from table where id in (' + @csv + ')'sp_executesql @sqlobviously
this will work fine if you pass in 1,2,3,4 into your list. but what if
some bad person sends you 1,2,3,4,null);select * from users; or
something more fun. yep,...