7.4. Viewing XML Data as Relational Data
OPENXML is a T-SQL function that provides access to in-memory XML documents through a mechanism similar to a relational result set. OPENXML can be used in SELECT and SELECT INTO statements wherever rowset providers such as a table or view, or the OPENROWSET function, can appear.
Before you can use OPENXML, you must call the system stored procedure sp_xml_preparedocument to parse the XML document and return the handle of the parsed internal representation of the document. The document handle is passed to OPENXML, which provides a relational rowset view of the document.
The syntax of the OPENXML keyword is:
OPENXML( idoc, rowpattern, [flags ] ) [ WITH ( <schemaDeclaration> [ ,...n ] | tableName ) ] <schemaDeclaration> ::= colName colType [colPattern | metaProperty]
If the WITH clause is not specified, the results are returned in edge table format instead of a rowset format. An edge table represents an XML document in a single table with the structure described in Table 7-3.
The syntax of the sp_xml_preparedocument system stored procedure is:
sp_xml_preparedocument hDoc OUTPUT [ , xmlText ] [ , xpathNamespaces ]
Once you have finished using the internal representation of the document, call the sp_xml_removedocument system stored procedure to remove it and invalidate the document handle. The syntax of sp_xml_removedocument is:
The following example uses OPENXML to extract manufacturing location information for product model 7 from the Instructions xml data type column in the Production.ProductModel table in AdventureWorks. An excerpt of the data follows:
<root xmlns="http://schemas.microsoft.com/sqlserver/2004/07/ adventure-works/ProductModelManuInstructions"> Adventure Works CyclesFR-210B Instructions for Manufacturing HL Touring FrameSummaryThis document contains manufacturing instructions for manufacturing the HL Touring Frame, Product Model 7. Instructions are ... <Location LaborHours="2.5" LotSize="100" MachineHours="3" SetupHours="0.5" Location>Work Center - 10 Frame FormingThe following instructions pertain to Work Center 10. (Setup hours = .5, Labor Hours = 2.5, Machine Hours = 3, Lot Sizing = 100) <step>Insert <material>aluminum sheet MS-2341</material> into the <tool>T-85A framing tool</tool> . </step> ... </Location> <Location LaborHours="1.75" LotSize="1" MachineHours="2" SetupHours="0.15" Location>Work Center 20 - Frame WeldingThe following instructions pertain to Work Center 20. (Setup hours = .15, Labor Hours = 1.75, . </Location> ... </root>
Execute the following statement to extract manufacturing location information for product model ID 7 as a tabular result set. Note that you must enter the emphasized line in the example on a single line rather than on two lines, done here only to fit the page width.
USE AdventureWorks DECLARE @idoc int DECLARE @instructions xml SET @instructions = (SELECT Instructions FROM Production.ProductModel WHERE ProductModelID = 7) EXEC sp_xml_preparedocument @idoc OUTPUT, @instructions, N'<root xmlns:ns="http://schemas.microsoft.com/sqlserver/2004/07/ adventure-works/ProductModelManuInstructions" />' SELECT * FROM OPENXML (@idoc, N'/ns:root/ns:Location') WITH ( LaborHours float N'@LaborHours', LotSize float '@LotSize', MachineHours float '@MachineHours', SetupHours float '@SetupHours', LocationID int '@LocationID' ) EXEC sp_xml_removedocument @idoc
The result set is shown in Figure 7-10.
Figure 7-10. Result set for OPENXML example