sql-serverssms-17

xml.node how to get value


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?


Solution

  • 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);