SQL Question Statement "With"

[vuyiswamb]

Good Afternoon Guys

i have the Following Query

SELECT * 
INTO #Rows
FROM OPENXML ( @doc , '/Timetable/Rows' , 2) 
WITH ( 	ID int,
	Descr varchar(64),
	StartTime smalldatetime,
	Duration int,
	Offs varchar(64)
)

I understand the First Part

SELECT * INTO #Rows FROM OPENXML ( @doc , '/Timetable/Rows' , 2)

but i dont understand Why With ? can someone Explain what is With Doig with Columns

Thank you

[szlamany]

My guess would be that's the table definition of the #ROWS temp table - right?

Did you pre-create that table prior to executing that statement?

Where did you see this statement - was it in MSDN documentation?

This is MS SQL Server - right?

[MattP]

In T-SQL the WITH statement is used to create CTEs (Common Table Expressions) allowing you to access a temporary named resultset.

I believe the WITH statement is only available on SQL Server 2005+.

MSDN article on CTEs: WITH common_table_expression (Transact-SQL)

Another MSDN article: Using Common Table Expressions

[szlamany]

Welcome to the forum!

Your second link states the syntax of a CTE is

The basic syntax structure for a CTE is:

WITH expression_name [ ( column_name [,...n] ) ]

AS

( CTE_query_definition )

The example posted by the OP doesn't have that format - it's not a NAMED expression...

The WITH keyword is used in many places in T-SQL - in this case it's supplying METADATA to the OPENXML.

http://msdn.microsoft.com/en-us/libr...8(SQL.90).aspx

OPENXML( idoc int [ in] , rowpattern nvarchar [ in ] , [ flags byte [ in ] ] )
[ WITH ( SchemaDeclaration | TableName ) ]

WITH is optional with OPENXML - from that same link above

The WITH clause provides a rowset format (and additional mapping information as required) by using either SchemaDeclaration or specifying an existing TableName. If the optional WITH clause is not specified, the results are returned in an edge table format. Edge tables represent the fine-grained XML document structure (such as element/attribute names, the document hierarchy, the namespaces, PIs, and son on) in a single table.

That's why I asked the OP where they found this syntax - because the doc at MSDN is already pretty clear on it's use and purpose related to OPENXML

[MattP]

The WITH keyword is used in many places in T-SQL - in this case it's supplying METADATA to the OPENXML.

Oops my brain completely missed the OPENXML in the initial post. Yes, the WITH statement provides the document structure for OPENXML in this case.

Thank you for the correction.