Posts

Showing posts with the label Shredding XML

Shredding XML using OPENXML: Convert XML to Rowset part II

Image
In this previous blog post, we learned how to use the OPENXML function to covert XML nodes to a single column in tabular format. Today, we'll learn how to convert all the nodes in a XML document to tabular format. We'll use the same XML document on technet with some modifications for the examples below. -- prepare XML document DECLARE @XmlDoc XML = N'<bookstore specialty="novel"> <book style="autobiography"> <title>Create a Vision</title> <author> <first-name>Joe</first-name> <last-name>Smith</last-name> </author> <price>12</price> </book> <book style="textbook"> <title>XML Reference</title> <author first-name="Mary" last-name="Jane" /> <price>55</price> </book> <book style="novel" id="myfave"> <title>Paths to Success...

Shredding XML using OPENXML: Convert XML to Rowset part I

Image
After learning about XPath expression , today we'll use what we learned in previous posts. The examples below show converting XML document to tabular format. This process is sometimes called shredding XML. We'll use the same sample XML document as last post with some modifications. Here's the OPENXML function syntax:    OPENXML (idoc int [in], rowpattern nvarchar [in], [flags byte [in]])   [WITH (ColumnName ColumnType [ColumnPattern | MetaProperty] | TableName)] Arguments   idoc :            Document handle created by sys.sp_xml_preparedocument stored procedure   rowpattern : XPath expression to identify the nodes.   flags :           Is optional.  This flag indicates what type of nodes we want to retrieve from the XML document, attributes only, elements only, or both.     byte value 0 = defaults. Select attributes.     byte value 1 = select attributes. If comb...