...and master of none

In my last post , I demonstrated how to shape and bend XML from your existing tables in SQL Server. In this post I am going to go the other direction and shred the xml directly into SQL tables with a desired structure. I will also create a parent-child relationship between entities where no keys previously existed in the xml. This approach uses the older OPENXML mechanisms and is distinct from the value() and node() methods introduced later. I believe this approach to be superior and easier to construct than that demonstrated by Adam Machanic . I'm assuming here that you've loaded the xml into the @xmlDocument variable (as in my previous example) You need a document handle (@XmlDocumentHandle). As long as this handle remains open, the id's returned for each node do not change. Use the OPENXML function with the @XmlDocumentHandle and create an XPath query that finds the data for the you want. The value of 9 means to map attributes to your defined table, and handles r...

The XML functionality of SQL Server is vast, however, the best subset of that functionality that I've found may be demonstrated in the following example: Unfortunately, the example result is listed before the code. Join all the desired tables together Set the desired root element Set the path to an empty string to avoid an extra XML element at the root of each resulting row Provide an XPATH name with attributes for each of the desired columns (select * doesn't work well) Keep all the selected columns together by XPATH name or you'll split your xml attributes See my next blog entry for my best way to shred the xml

Say you have a stored procedure that does something like this: SELECT A.id, 10.0 * A.number / B.number AS Computed FROM Table1 A JOIN Table2 B on A.id = B.id What is the resulting type of the "Computed" column? Decimal? Float? The following SQL 2012 will tell you the data types of the columns from the first returned data set of a stored procedure: SELECT name, system_type_name FROM sys.dm_exec_describe_first_result_set_for_object (OBJECT_ID('myNamespace.mySPROC') NULL ); This will give a nice table that contains the column name and system data type name system_type_name --------------------- ---------------------- Id bigint Computed float

Here is the creation script for a neat function that will generate a table full of every date in the given range. -- ============================================= -- Author: Aaron D. Wells -- Create date: 9/23/2010 -- Description: Get a list of dates in a range -- ============================================= CREATE FUNCTION fn_Dates (@fromDate date, @toDate date) RETURNS @dateTable TABLE( [Date] Date NOT NULL, FiscalYear int NOT NULL, FiscalMonth int NOT NULL ) AS BEGIN WITH CTE_DatesTable([date]) AS ( SELECT @fromDate AS [date] UNION ALL SELECT DATEADD(dd, 1, [date]) FROM CTE_DatesTable WHERE DATEADD(dd, 1, [date]) 9 THEN YEAR(date) + 1 ELSE YEAR(date) END FiscalYear, CASE WHEN MONTH(date) > 9 THEN MONTH(date) - 9 ELSE MONTH(date) + 3 END FiscalMonth FROM CTE_DatesTable OPTION (MAXRECURSION 0) RETURN END Using the function is pretty simple, just give it a starting and ending date. By the w...