I have table which store xml data in below format to a nvarchar(max)column.Want to query this column to get property information.
From xml text in question, it looks like there are 2 pieces if information available for each property, namely the Identity attribute and the value of the node. Some of the Property nodes have blank values. Here is the sample script which return 2 columns for each occurrence of the Property node in sample xml:
The sample script above produces the following result:
Suppose that there is a table named SomeTable which, sadly, includes the nvarchar type rather than xml type column named SomeColumn. Because it is not possible to cast the the nvarchar column as xml and immediately use .nodes on it, it will take to first write a subselect (or CTE) which has this column cast as xml and only then cross apply. For the sake of completeness, I will assume that the table has some other column, lets call it RecordID, which also needs to be included in the results:
The select statement above will return 3 columns and as many records per each RecordID as the number of Property node occurrences in the value of SomeColumn column.
Hope this helps.
answered Aug 10, 2017 at 12:59 PM