Robin Riversong - Thursday, February 23, 2017 1:11 PM

drew.allen - Wednesday, February 22, 2017 3:50 PM

It's because all of your values are defined using absolute paths instead of relative paths.  Only the nodes definition should use an absolute path.  Changing to relative paths would allow you to get rid of the loop and the dynamic sql.

Also, your code is showing with emoticons in some places.  I believe it's because you did not use the SQL Code tags that are recommended.

Drew

Hi Drew, Thank you so much for reading and replying to my post.. I apologize for the in line code and weird emos, I used the SQL Code tags to box up the code, but can't seem to get the emos to go away..

As I mentioned, I'm pretty dense about shredding..so I have some follow on questions for you..

The dynamic code is so it will iterate through each instance of the Education node for a single provider record.. The attached files are an XML example of what this code is trying to shred, and the results of shredding that example xml.. Note that there are 5 instances of Education for this one provider record, we want to shred all 5 in to a SQL table, then move on to the next provider.

Since I am querying a blob in a sql table rather than the xml doc itself, I'm not sure I understand your comment regarding absolute vs relative path in this context.. here is that same code in a static (not dynamic) version.

When I run this code, as expected because of the static singleton, I get only the first instance of the education node.. no way to iterate through.  If I remove the singleton, the code breaks., can you show me how this code should look using relative path and without the singleton?

Thanks again..
R


WITH XMLNAMESPACES('urn:GeoAccess.PDE.Provider' as hr
,DEFAULT'http:////www.w3.org/2001/XMLSchema')

INSERT INTO ProviderEducationTest
SELECT Distinct
  T.rows.value('(/hr:Provider/hr:NPI)[1]','varchar(40)') AS [NPI]
  , T.rows.value('(/hr:Provider/hr:LastName)[1] ','varchar(40)') AS [LastName]
  ,T.rows.value('(/hr:Provider/hr:Education/hr:InstitutionName)[1]','varchar(500)') AS [InstitutionName]
  ,T.rows.value('(/hr:Provider/hr:Education/hr:City)[1]','varchar(500)') AS [City]
  ,T.rows.value('(/hr:Provider/hr:Education/hr:State)[1]','varchar(500)') AS [State]
  ,T.rows.value('(/hr:Provider/hr:Education/hr:StartDate)[1]','datetime') AS [StartDate]
  ,T.rows.value('(/hr:Provider/hr:Education/hr:EndDate)[1]','datetime') AS [EndDate]
  ,T.rows.value('(/hr:Provider/hr:Education/hr:CompletionDate)[1]','datetime') AS [CompletionDate]
    ,T.rows.value('(/hr:Provider/hr:Education/hr:Degree/hr:DegreeAbbreviation)[1]','varchar(40)') AS [DegreeAbbreviation]
    ,T.rows.value('(/hr:Provider/hr:Education/hr:ProgramCompletedFlag)[1]','varchar(40)') AS [ProgramCompletedFlag]
    , 1 AS [DegreeNumber]
FROM
  [dbo].[CAQH_Return_XML]
CROSS APPLY
  FileInfo.nodes('/hr:Provider/hr:Education/hr:InstitutionName') T(rows)
  where T.rows.value('(/hr:Provider/hr:NPI)[1]','varchar(40)') = 'xxxxxxxxxx';