.NET Code Catto Blogger Blog: SQL 2008 DB Dev 70-433 XML Data

Hey Now Everybody,

This is a review of the sixth objective on the Microsoft exam 70-433 ‘Working with XML Data’

While reviewing for the Microsoft exams it’s enjoyable to combine the three sections:
1. Self Paces Book’s chapters content
2. Code, SQL Scripts
3. Concepts from Questions of the Microsoft Training Kit

6 Working with XML Data (12 percent)
6.1 Retrieve Relational Data as XML c7.1
6.2 Transform XML data into relational data c7.1
6.3 Query XML data c7.1
6.4 Manage XML data c7.1

Chapter 7.1 Key Concepts
XML can be generated using SELECT in four different modes:
For XML Raw
, For XML Auto
, For XML PATH
, For XML Explicit
For XML Path is typically the preferred mode used to generate XML
XML Data type can be either untyped or typed (validated by an XML schema collection)
Untyped XML data type all values are always interpreted as strings
We can use value, query, exist, nodes & modify methods to query & alter XML data types
Code
Query in ASP.NET Membership database querying aspnet_users table


 /****** Script for SelectTopNRows command from SSMS ******/  
SELECT [UserId]      
     ,[UserName]    
FROM [ccatto_aspnetdb].[dbo].[aspnet_Users]    
FOR XML AUTO, ELEMENTS

Here are some of the results:

Here is another example using For XML Path


/****** Script for SelectTopNRows command from SSMS ******/   
SELECT [UserId] as uid    
  ,[UserName] as "info/@userName"    
FROM [ccatto_aspnetdb].[dbo].[aspnet_Users]    
FOR XML PATH('K8Users');

Here is a sample node from the results:

Example of SHOWPLAN XML to show execution plan


/****** Script for SelectTopNRows command from SSMS ******/   
SET SHOWPLAN_XML ON;    
GO    
SELECT [UserId] as uid    
 ,[UserName] as "info/@userName"
FROM [ccatto_aspnetdb].[dbo].[aspnet_Users]    
FOR XML PATH('K8Users');     
Go    
SET SHOWPLAN_XML OFF;

Review Questions Concepts:

Create XML Schema Collection imports schema components to database

For XML Path determines the shape of resulting XML

Below are the skills measured in detail:

orking with XML Data (12 percent)

Retrieve relational data as XML.
FOR XML
Transform XML data into relational data.
OPENXML
, sp_xml_preparedocument
, sp_xml_removedocument
Query XML data.
XQUERY
, XPATH
Manage XML data.
XML datatype
, XML indexes
, XML schema collections