The article in the simple-talk newsletter continues on talking about XML shredding, specifically using XQUERY rather than OPENXML. The result of the shredding is that the XML is deconstructed into a typical database table.
Basically, you do a traditional select statement from a table that includes columns of XML data, but you specify the columnname.value(specification) to pull the specified XML data (value) from the XML column. The specification looks like
column-name.value(‘declare namespace ns=”http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey”; (/ns:IndividualSurvey/ns:elementname)[1]’,’varchar(50)’) AS ElementName,…
Notice that we name the column we want, append the .value operator, declare the namespec, give the query the XPATH to what we want, and finally the SQLType of the data that we want to cast\convert to. And the name of the column as an alias.
Note that the value operator demands that we have a singleton as a return – not sure about null, but we can’t return more than one item. Which works in this case since this is row by row.
becknspace 