Excluding nodes from XML data before returning from SQL Server

This post follows on from a question I recently replied to, for how to exclude a specific node from an XML column value before returning it, using TSQL. Example setup CREATETABLEExample(IDINTEGERIDENTITY(1,1)PRIMARYKEY,XmlFieldXML)INSERTExample(XmlField)VALUES('<Root><ChildA>Value I want to see</ChildA><ChildB>Value I do not want to see</ChildB></Root>')So if you want to return the XML minus the ChildB node, how do you do it? Modify it Literally, using the modify method that is supported on the XML data type. [Read More]

SQL Server XML datatype with CDATA

So today I learnt something new - it turns out the XML datatype in SQL Server does not preserve CDATA sections.
e.g.

DECLARE @XML XML  
SET @XML = '<Test><NodeA><![CDATA[Testing cdata section <woop!>]]></NodeA></Test>'  
SELECT @XML  
  
Results  
------------------------------------------------------------------  
<Test><NodeA>Testing cdata section &lt;woop!&gt;</NodeA></Test>  

After a quick dig around, I found this MS Connect case. I personally would like to see it accept whatever you pass in without silently altering it, as long as it’s well-formed XML of course.

SQL Server 2008 - Table Valued Parameters vs XML vs CSV

The scenario is, you want to create a stored procedure that returns the records relating to a finite set of keys/IDs that you pass in to it. How do you do it? Well you could use dynamic SQL, but for the purpose of this article I’m going to ignore that option. Test scenario -- Create basic customer table CREATETABLE[dbo].[Customer](IdINTEGERIDENTITY(1,1)PRIMARYKEY,NameNVARCHAR(50)NOTNULL);GO-- Populate Customer table with 100,000 sample records DECLARE@CounterINTEGERSET@Counter=1WHILE(@Counter<=100000)BEGININSERTCustomer(Name)VALUES('Test Customer #'+CAST(@CounterASVARCHAR(10)))SET@Counter=@Counter+1ENDOption 1 - CSV list of keys In SQL Server 2000, you would most likely have ended up passing in a CSV list of keys, splitting that out into a table, and then joining that on to your real data table to match the records to return. [Read More]