I know (usually) how to export the data from XML using SQL Server and I have done so for the rest of the extract, I just can't figure out how to get the values to line up when cross applying the stuff inside the XML tags.
This works and returns the XML tag name
DECLARE @XML XML
SET @XML= '
<Export>
<CustomInformation name="Customer ID">12345</CustomInformation>
<CustomInformation name="Prepaid">0.00</CustomInformation>
<CustomInformation name="New Amount">0.00</CustomInformation>
</Export>
'
select
Description = CustomInformation.value('@name','nvarchar(max)')
from
@XML.nodes('/Export/CustomInformation') as b(CustomInformation)
This returns null
DECLARE @XML XML
SET @XML= '
<Export>
<CustomInformation name="Customer ID">12345</CustomInformation>
<CustomInformation name="Prepaid">0.00</CustomInformation>
<CustomInformation name="New Amount">0.00</CustomInformation>
</Export>
'
select
Description = CustomInformation.value('@name','nvarchar(max)')
,tire_wheel = col2.value('@Prepaid', 'money')
from
@XML.nodes('/Export/CustomInformation') as b(CustomInformation)
cross apply b.CustomInformation.nodes('CustomInformation') as c(col2)
How do I get the data to line up to the tag name?
DECLARE @XML XML = N'
<Export>
<CustomInformation name="Customer ID">12345</CustomInformation>
<CustomInformation name="Prepaid">120.00</CustomInformation>
<CustomInformation name="New Amount">10.00</CustomInformation>
</Export>
<Export>
<CustomInformation name="Customer ID">789</CustomInformation>
<CustomInformation name="Prepaid">160.00</CustomInformation>
<CustomInformation name="New Amount">30.00</CustomInformation>
</Export>
';
--1
select
Description = CustomInformation.value('@name','nvarchar(max)'),
Value = CustomInformation.value('.', 'nvarchar(max)')
from @XML.nodes('./Export/CustomInformation') as c(CustomInformation);
--2
select
NodeID = dense_rank() over(order by b.Export), --use this to cross tab/pivot
Description = CustomInformation.value('@name','nvarchar(max)'),
Value = CustomInformation.value('.', 'nvarchar(max)')
from @XML.nodes('./Export') as b(Export)
cross apply b.Export.nodes('./CustomInformation') as c(CustomInformation);
--3
select *
from
(
select
NodeID = dense_rank() over(order by b.Export), --use this to cross tab/pivot
Description = CustomInformation.value('@name','nvarchar(max)'),
Value = CustomInformation.value('.', 'nvarchar(max)')
from @XML.nodes('./Export') as b(Export)
cross apply b.Export.nodes('./CustomInformation') as c(CustomInformation)
) as src
pivot
(
max(value) for Description in ([Customer Id], [Prepaid], [New Amount])
) as pv;
--??
select
Description = CustomInformation.value('@name','nvarchar(max)'),
Value = CustomInformation.value('.', 'nvarchar(max)'),
[Prepaid value ?] = p.Prepaid.value('.[1]', 'nvarchar(max)')
from @XML.nodes('./Export/CustomInformation') as c(CustomInformation)
--up one level in the hierarchy...not performant
outer apply c.CustomInformation.nodes('../CustomInformation[@name="Prepaid"]') as p(Prepaid);
--??
select
Description = CustomInformation.value('@name','nvarchar(max)'),
Value = CustomInformation.value('.', 'nvarchar(max)'),
[Prepaid value ?] = p.Prepaid.value('.[1]', 'nvarchar(max)')
from @XML.nodes('./Export') as b(Export)
cross apply b.Export.nodes('./CustomInformation') as c(CustomInformation)
outer apply b.Export.nodes('./CustomInformation[@name="Prepaid"]') as p(Prepaid);