INTERNET APPLICATION DEVELOPMENT
MID MARKET ERP DEVELOPMENT
by Sheila Zhang
Using Microsoft’s SQL Server 2012 makes querying with XML a lot easier. Microsoft’s SQL Server allows you to create XML with a SQL query or shred XML into a data table format. I have shared with you below examples of achieving this query process faster.
When you use the "FOR XML" clause in the "SELECT- T_SQL" statement, you convert a query record set to XML. It is that simple.
For example, by querying the following statement:
Also, by adding "for XML raw" to the end of the previous statement:
the returned row-sets convert every row to a single element named "row," and the columns translate to the attributes of this element, like this:
While this example appears as true XML, I actually need to include an additional level of nesting. For example, I use the following statement to incorporate nesting orders for each customer:
Another option is to use PATH as shown here:
I hope you find these basic methods of returning data output in XML format useful. Look for an upcoming blog post on producing complex output in XML format.
Thank you for reading our blog and have a great week.