IRL11 (8/30/2016)
Thank you for your reply, I have a feeling these files may be too much for XMLBulkLoader however I will have a look at the links you sent and see if I can get them to work. No doubt I will be back in touch shortly
This should get you a fair amount of the way there. This is a bit of brute force, but it does get you there:
;with structCTE as
(select --distinct
b.value('text()[1]','varchar(400)') as NodeValue,
b.value('local-name(.)','varchar(400)') as Node,
b.value('local-name(..)','varchar(400)') as l1,
b.value('local-name(../..)','varchar(400)') as l2,
b.value('local-name(../../..)','varchar(400)') as l3
,b.value('local-name(../../../..)','varchar(400)') as l4
,b.value('local-name(../../../../..)','varchar(400)') as l5
,b.value('local-name(../../../../../..)','varchar(400)') as l6
,b.value('local-name(../../../../../../..)','varchar(400)') as l7
,b.value('local-name(../../../../../../../..)','varchar(400)') as l8
,c.value('local-name(.)','varchar(400)') as attname
,c.value('.','varchar(400)') as attvalue
from (select top 1 * from #RsXMLLoad) x
cross apply [Message].nodes('//*') a(b)
outer apply b.nodes('@*') b(c)
),
XMLsummary as (
select case when l1='' then ''
when l2='' then l1+'/'
when l3='' then l2+'/'+l1+'/'
when l4='' then l3+'/'+l2+'/'+l1+'/'
when l5='' then l4+'/'+l3+'/'+l2+'/'+l1+'/'
when l6='' then l5+'/'+l4+'/'+l3+'/'+l2+'/'+l1+'/'
when l7='' then l6+'/'+l5+'/'+l4+'/'+l3+'/'+l2+'/'+l1+'/'
when l8='' then l7+'/'+l6+'/'+l5+'/'+l4+'/'+l3+'/'+l2+'/'+l1+'/'
else l8+'/'+l7+'/'+l6+'/'+l5+'/'+l4+'/'+l3+'/'+l2+'/'+l1+'/'
endxpath,
case when l1='' then 1
when l2='' then 2
when l3='' then 3
when l4='' then 4
when l5='' then 5
when l6='' then 6
when l7='' then 7
when l8='' then 8
else 9
endxml_element_level,
case when l1='' then 0
when l2='' then 1
when l3='' then 2
when l4='' then 3
when l5='' then 4
when l6='' then 5
when l7='' then 6
when l8='' then 7
else 8
endxml_parent_level,
Node,
nodevalue,nullif(attname,'') attname,attvalue
from structCTE s)
select ROW_NUMBER() over (order by (select null)) Rn,
xml_parent_level,
xml_element_level,
xpath+Node xml_path,
xpath xml_parent_path,
Node xml_data_name,
NodeValue ElementValue,
Attname as AttributeName,
AttValue as AttributeValue
from XMLsummary
Note: this is hardcoded to handle a max depth of 9 (to the "deepest element"). You could certainly add more levels if you need although I'd question the sanity of transport model if it goes a lot deeper.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?