Hello Des ,
Your solution works brilliantly, thank you so much! Sorry about the image of xml.. wanted to use color coding to illustrate the issue.. Also although I troll forums from time to time, I hardly ever post, so still figuring out forum etiquette.
I can see what I need to do next (load the Table Variable from the blob rather than from static xml) but because I am new to shredding, I have no idea how to accomplish this.
The xml is VERY inconsistent, I can see that there is an opening tag that contains an ID attribute <Education ID="1000"> for each instance of this node. Most of time, but not all of the time, the first ID attribute is 1000.. but not always..
So, as near as I can figure, I want to load the table variable with every instance of the xml that follows the Education ID attribute opening tag through the last closing tag </Education> Alternately, every education node does appear to consistently have an <InstitutionName> element that appears to always have a value (you can see the guy I inherited this code from was using the CROSS APPLY to get the nodes associated with the <InstitiutionName> element in the loop through)
Do you have any suggestions as to how to load the Table Variable from the blob stored in a SQL table (CAQH_Return_XML) with just the nodes I'm interested in.. in this case every node that starts with an education ID attribute (ex: ) <Education ID="1000">? or that contains the <InstitutionName> element?
Thanks again for your help on this.. invaluable..
You do not need to use a table variable. I simply used one to provide a sample in a similar format to your original question.
Try using the code directly against your table.
SELECT
T.rows.value('(../NPI/text())[1]','varchar(40)') AS [NPI]
, T.rows.value('(../LastName/text())[1] ','varchar(40)') AS [LastName]
, T.rows.value('(InstitutionName/text())[1]','varchar(500)') AS [InstitutionName]
, T.rows.value('(City/text())[1]','varchar(500)') AS [City]
, T.rows.value('(State/text())[1]','varchar(500)') AS [State]
, T.rows.value('(StartDate/text())[1]','date') AS [StartDate]
, T.rows.value('(EndDate/text())[1]','date') AS [EndDate]
, T.rows.value('(CompletionDate/text())[1]','date') AS [CompletionDate]
, T.rows.value('(Degree/DegreeAbbreviation/text())[1]','varchar(40)') AS [DegreeAbbreviation]
, T.rows.value('(ProgramCompletedFlag/text())[1]','varchar(40)') AS [ProgramCompletedFlag]
, 1 AS [DegreeNumber]
FROM dbo.CAQH_Return_XML
CROSS APPLY FileInfo.nodes('Provider/Education') T(rows);
If this doesn't work, please provide us with a complete section of XML. It must contain enough for us to see the path into the specific nodes in question.