sql xml shredding, attributes, elements and levels
TRANSCRIPT
XMLShredding, Attributes Elements and Levels
Presented by Aaron Buma
Session Overview
• Shredding XML• Attributes• Elements• Levels
Shredding XML
• XML SQL SERVER Tabular Data• “XML Shredding”: extracting values from XML documents for
storage in a relational database• sp_XML_PrepareDocument – loads XML into memory– Returns plan handle
Shredding XML
• Removing a plan from memory– sp_XML_RemoveDocument #HandleID– Closing Session (available after SQL 2000)
Shredding XML
• OpenXML(Plan Handle, Row Pattern)– Plan Handle is the output from sp_XML_PrepareDocument – Row Pattern is the level of XML you want returned as data
Demo
Row and Column Patterning
• Returning matching Row Patterns: use ‘//’ notation
• Column Patterns from higher levels: use ‘../’ notation
OpenXML Options
• With options you can define the default column type as: Attributes (1), Elements (2), Attributes and Elements (3)
• You can still define columns as Attributes or Elements if needed
Demo
Questions?
• Any Questions?
Contact: [email protected]
Additional Free SQL Trainings at:http://www.aaronbuma.com/category/sql-training/