SIMULATION
You have a data warehouse that contains the data for all the customers of your company.
You need to create a query dynamically generates a SELECT statement from a table named CUSTOMERS. The SELECT statement must generate a full list of columns.
Part of the correct Transact-SQL has been provided in the answer area below. Enter the code in the answer area that resolves the problem and meets the stated goals or requirements. You can add code within the code that has been provided as well as below it.
Use the Check Syntax button to verify your work. Any syntax or spelling errors will be reported by line and character position
Your Response: tvpe here
A. "XML PATH"
Correct Answer: A
Explanation/Reference:
Explanation:
In line 7 add XML PATH to get thefollowing line:
FOR XML PATH (”)), 1,1,”) +
Here is how it works:
1.Get XML element string with FOR XML
Adding FOR XML PATH to the end of a query allows you to output the results of the query as XML elements, with the element name contained inthe PATH argument. For example, if we were to run the following statement:
SELECT ‘,’ + name
FROM tempi
FOR XML PATH (")
By passing in a blank string (FOR XML PATH(")), we get the following instead:
,aaa,bbb,ccc,ddd,eee
2.Remove leading commawith STUFF
The STUFF statement literally "stuffs" one string into another, replacing characters within the first string. We, however, are using it simply to remove the first character of the resultant list of values.
SELECT abc = STUFF((
SELECT V+NAME
FROM tempi
FOR XML PATH(”)
), 1, 1, ")
FROM temp1
Note: The full code will be:
SELECT ‘SELECT’+
STUFF ((
SELECT ‘,[‘ + name + ‘]’
FROM
WHERE id = OBJECTJD(‘Customers’) AND
…name <> ‘me’
FOR XML PATH (”), 1, 1,”) +
‘FROM[Customers]’
References: http://stackoverflow.com/questions/31211506/how-stuff-and-for-xml-path-work-in-sql-server
