Chapter 5 ADVANCED DATA RETRIEVAL AND MODIFICATION 303
Chapter 5 ADVANCED DATA RETRIEVAL AND MODIFICATION 303 You can enter the following if you want to use the rowpattern /Person/Sales to return sales information, and show the first name and last name for each sale: SELECT * FROM openxml(@hdoc, /Person/Sales , 1) WITH (FName varchar(30) ../@FirstName , LName varchar(30) ../@LastName , QtyPurchased int @QtyPurchased ) Notice the use of the .. syntax in specifying the output of the XML file. This SELECT statement uses the /Person/Sales as the default level, so any value that exists at that level can be specified by just the name of the value. Here s the output: FName LName QtyPurchased Shelly Alexander 5 Shelly Alexander 10 Anything above or below the default level, /Person/Sales, has to be qualified with a path, which is what the .. syntax represents: a path representing the level above the default, in this case /Person. Here s another example: select * from openxml(@hdoc, /Person , 1) WITH (FName varchar(30) @FirstName , LName varchar(30) @LastName , QtyPurchased int Sales/@QtyPurchased ) TIPPathing XML Pathing XML is very likely to be on your exam because it s important to understand how it works if you re going to use OPENXML(), and because Microsoft is very proud of the new XML features in SQL Server 2000. EXAM This shows a different syntax, and provides a different result. The preceding example returned every person and sale. This example returns only the first sale: FName LName QtyPurchased Shelly Alexander 5 It returns only the first sale because it s returning one row for each default level, which is the /Person level in this case. So, now you know how to export data to XML format, which is a pretty useful thing. You also should have a good handle on how to translate data from XML into a rowset, which is marginally useful. So now it s time to move real data in and out of SQL Server.
For high quality java hosting services please check java web hosting website.