Chapter 5 ADVANCED DATA RETRIEVAL AND MODIFICATION 301 Using OPENXML The OPENXML function has three parameters and an optional WITH clause. It takes the document handle, which is returned by the sp_xml_preparedocument procedure, the rowpattern, which specifies which rows to return, and a single byte value that can specify flags. Here s an expansion of the preceding example: DECLARE @hdoc INT EXEC sp_xml_preparedocument @hdoc OUTPUT, select * from openxml(@hdoc, * ) EXEC sp_xml_removedocument @hdoc This returns a nearly useless recordset, because it isn t formatted. Here s a little bit of the recordset: id parentid nodetype localname prefix namespaceuri datatype prev text 0 NULL 1 Person NULL NULL NULL NULL NULL 2 0 2 PersonID NULL NULL NULL NULL NULL 9 2 3 #text NULL NULL NULL NULL 9 Not very easy to work with. That s why there s a rowset filter, like this: SELECT * FROM openxml(@hdoc, /Person , 1) This rowset filter returns another nearly useless rowset, but at least it s a shorter, nearly useless rowset; it contains only the attributes about the person. Now, if you could just put the rows where they need to be, you d have it made: SELECT * FROM openxml(@hdoc, /Person , 1) WITH (FirstName varchar(30), LastName varchar(30)) That WITH clause does the trick, and you finally get decent output. FirstName LastName Shelly Alexander (1 row(s) affected)
If you looking for unlimited one inclusive web hosting plan please check unlimited web hosting website.