none
Retrieve data from XML using SELECT

    Question

  • Gurus,

     

     

    declare @test xml;


    set @test = '<xml xmlns:xsi="http://www.w3.org/2001/XMLSchema">
    <ArrayOfURDTO>
    <URDTO>
    <AppLayer>UI</AppLayer>
    <AmountYear1>0</AmountYear1>
    <AmountYear2>0</AmountYear2>
    <AmountYear3>0</AmountYear3>
    <TypeID>5</TypeID>
    <IsUpdateable>false</IsUpdateable>
    <PercentageAppliedRateableBillings>3.00</PercentageAppliedRateableBillings>
    <ThreeYearAverageRateableBillings>0</ThreeYearAverageRateableBillings>
    <ThreeYearAverageTurnover>0</ThreeYearAverageTurnover>
    <Year1 xsi:nil="true" />
    <Year2 xsi:nil="true" />
    <Year3 xsi:nil="true" />
    </URDTO>
    <URDTO>
    <AppLayer>UI</AppLayer>
    <AmountYear1>0</AmountYear1>
    <AmountYear2>0</AmountYear2>
    <AmountYear3>0</AmountYear3>
    <TypeID>6</TypeID>
    <IsUpdateable>false</IsUpdateable>
    <PercentageAppliedRateableBillings>7.50</PercentageAppliedRateableBillings>
    <ThreeYearAverageRateableBillings>0</ThreeYearAverageRateableBillings>
    <ThreeYearAverageTurnover>0</ThreeYearAverageTurnover>
    <Year1 xsi:nil="true" />
    <Year2 xsi:nil="true" />
    <Year3 xsi:nil="true" />
    </URDTO>
    </ArrayOfURDTO>
    </xml>';

    SELECT

     

    main

    .nodes.value('AmountYear1[1]', 'DECIMAL') AS Year1Amount,

    main

    .nodes.value('AmountYear2[1]', 'DECIMAL') AS Year2Amount,

    main

    .nodes.value('AmountYear3[1]', 'DECIMAL') AS Year3Amount,

    main

    .nodes.value('PercentageAppliedRateableBillings[1]', 'DECIMAL') AS PercentRate,

    main

    .nodes.value('ThreeYearAverageTurnover[1]', 'DECIMAL') AS AverageAmount,

    main

    .nodes.value('ThreeYearAverageRateableBillings[1]', 'DECIMAL') AS AveragePercentRate,

    main

    .nodes.value('Year1[1]', 'DECIMAL') AS Year1,

    main

    .nodes.value('Year2[1]', 'DECIMAL') AS Year2,

    main

    .nodes.value('Year3[1]', 'DECIMAL') AS Year3,

    main

    .nodes.value('TypeID[1]', 'INT') AS ID INTO #tempTr

    FROM

     

    @test.nodes('ArrayOfURDTO/URDTO') AS main(nodes)

    I am trying to fetch data, but it is coming "0" rows. My requirement is i need to get 2 rows and whereever it is "xsi:nil" I need to populate "NULL.

    Looking forward for help.

    Thanks,

    Saravanan R

    Saturday, March 27, 2010 12:42 PM

Answers

  • The reason you are getting 0 rows is you have mentioned @test.nodes('ArrayOfURDTO/URDTO') AS main(nodes) but the root node is not ArrayOdURDTO. So you will need to mention @test.nodes('//ArrayOfURDTO/URDTO') AS main(nodes). 

    Then to get 0 for xsi:nil, you will have to use a case statement. The following query should work.

     

    SELECT

    main.nodes.value('AmountYear1[1]', 'DECIMAL') AS Year1Amount,

    main.nodes.value('AmountYear2[1]', 'DECIMAL') AS Year2Amount,

    main.nodes.value('AmountYear3[1]', 'DECIMAL') AS Year3Amount,

    main.nodes.value('PercentageAppliedRateableBillings[1]', 'DECIMAL') AS PercentRate,

    main.nodes.value('ThreeYearAverageTurnover[1]', 'DECIMAL') AS AverageAmount,

    main.nodes.value('ThreeYearAverageRateableBillings[1]', 'DECIMAL') AS AveragePercentRate,

    case when main.nodes.value('Year1[1]', 'VARCHAR(MAX)') = '' then 0 

    else main.nodes.value('Year1[1]', 'VARCHAR(MAX)') end AS Year1,

    case when main.nodes.value('Year2[1]', 'VARCHAR(MAX)') = '' then 0 

    else main.nodes.value('Year2[1]', 'VARCHAR(MAX)') end AS Year2,

    case when main.nodes.value('Year3[1]', 'VARCHAR(MAX)') = '' then 0 

    else main.nodes.value('Year3[1]', 'VARCHAR(MAX)') end AS Year3,

     

    main.nodes.value('TypeID[1]', 'INT') AS ID --INTO #tempTr

    FROM @test.nodes('//ArrayOfURDTO/URDTO') AS main(nodes)

     

     


    Maeenul
    My Programming Site
    My Blog
    • Marked as answer by KJian_ Monday, April 05, 2010 8:57 AM
    Saturday, March 27, 2010 6:00 PM
  • Hello,

    Try this...

    SELECT main.nodes.value('AmountYear1[1]', 'DECIMAL') AS Year1Amount,
    main.nodes.value('AmountYear2[1]', 'DECIMAL') AS Year2Amount,
    main.nodes.value('AmountYear3[1]', 'DECIMAL') AS Year3Amount,
    main.nodes.value('PercentageAppliedRateableBillings[1]', 'DECIMAL') AS PercentRate, 
    main.nodes.value('ThreeYearAverageTurnover[1]', 'DECIMAL') AS AverageAmount,
    main.nodes.value('ThreeYearAverageRateableBillings[1]', 'DECIMAL') AS AveragePercentRate,
    CONVERT(DECIMAL,main.nodes.value('Year1[1]', 'INT')) AS Year1,
    CONVERT(DECIMAL,main.nodes.value('Year2[1]', 'INT')) AS Year2,
    CONVERT(DECIMAL,main.nodes.value('Year3[1]', 'INT')) AS Year3,
    main.nodes.value('TypeID[1]', 'INT') AS ID 
    FROM @test.nodes('xml/ArrayOfURDTO/URDTO') AS main(nodes)
    

    Hope its helpful...

     


    Pavan
    • Marked as answer by KJian_ Monday, April 05, 2010 8:57 AM
    Wednesday, March 31, 2010 9:44 AM

All replies

  • The reason you are getting 0 rows is you have mentioned @test.nodes('ArrayOfURDTO/URDTO') AS main(nodes) but the root node is not ArrayOdURDTO. So you will need to mention @test.nodes('//ArrayOfURDTO/URDTO') AS main(nodes). 

    Then to get 0 for xsi:nil, you will have to use a case statement. The following query should work.

     

    SELECT

    main.nodes.value('AmountYear1[1]', 'DECIMAL') AS Year1Amount,

    main.nodes.value('AmountYear2[1]', 'DECIMAL') AS Year2Amount,

    main.nodes.value('AmountYear3[1]', 'DECIMAL') AS Year3Amount,

    main.nodes.value('PercentageAppliedRateableBillings[1]', 'DECIMAL') AS PercentRate,

    main.nodes.value('ThreeYearAverageTurnover[1]', 'DECIMAL') AS AverageAmount,

    main.nodes.value('ThreeYearAverageRateableBillings[1]', 'DECIMAL') AS AveragePercentRate,

    case when main.nodes.value('Year1[1]', 'VARCHAR(MAX)') = '' then 0 

    else main.nodes.value('Year1[1]', 'VARCHAR(MAX)') end AS Year1,

    case when main.nodes.value('Year2[1]', 'VARCHAR(MAX)') = '' then 0 

    else main.nodes.value('Year2[1]', 'VARCHAR(MAX)') end AS Year2,

    case when main.nodes.value('Year3[1]', 'VARCHAR(MAX)') = '' then 0 

    else main.nodes.value('Year3[1]', 'VARCHAR(MAX)') end AS Year3,

     

    main.nodes.value('TypeID[1]', 'INT') AS ID --INTO #tempTr

    FROM @test.nodes('//ArrayOfURDTO/URDTO') AS main(nodes)

     

     


    Maeenul
    My Programming Site
    My Blog
    • Marked as answer by KJian_ Monday, April 05, 2010 8:57 AM
    Saturday, March 27, 2010 6:00 PM
  • Hello,

    Try this...

    SELECT main.nodes.value('AmountYear1[1]', 'DECIMAL') AS Year1Amount,
    main.nodes.value('AmountYear2[1]', 'DECIMAL') AS Year2Amount,
    main.nodes.value('AmountYear3[1]', 'DECIMAL') AS Year3Amount,
    main.nodes.value('PercentageAppliedRateableBillings[1]', 'DECIMAL') AS PercentRate, 
    main.nodes.value('ThreeYearAverageTurnover[1]', 'DECIMAL') AS AverageAmount,
    main.nodes.value('ThreeYearAverageRateableBillings[1]', 'DECIMAL') AS AveragePercentRate,
    CONVERT(DECIMAL,main.nodes.value('Year1[1]', 'INT')) AS Year1,
    CONVERT(DECIMAL,main.nodes.value('Year2[1]', 'INT')) AS Year2,
    CONVERT(DECIMAL,main.nodes.value('Year3[1]', 'INT')) AS Year3,
    main.nodes.value('TypeID[1]', 'INT') AS ID 
    FROM @test.nodes('xml/ArrayOfURDTO/URDTO') AS main(nodes)
    

    Hope its helpful...

     


    Pavan
    • Marked as answer by KJian_ Monday, April 05, 2010 8:57 AM
    Wednesday, March 31, 2010 9:44 AM
  • Below query will return null for xsi:nil

     

    SELECT     main.nodes.value('AmountYear1[1]', 'DECIMAL') AS Year1Amount,
            main.nodes.value('AmountYear2[1]', 'DECIMAL') AS Year2Amount,
            main.nodes.value('AmountYear3[1]', 'DECIMAL') AS Year3Amount,
            main.nodes.value('PercentageAppliedRateableBillings[1]', 'DECIMAL') AS PercentRate,
            main.nodes.value('ThreeYearAverageTurnover[1]', 'DECIMAL') AS AverageAmount,
            main.nodes.value('ThreeYearAverageRateableBillings[1]', 'DECIMAL') AS AveragePercentRate,
            CONVERT(DECIMAL,NULLIF((CASE main.nodes.value('Year1[1]/@xsi:nil="true"', 'bit')
                                        WHEN 1 THEN NULL
                                        ELSE main.nodes.value('Year1[1]', 'INT')
                                     END),0)) AS Year1,
            CONVERT(DECIMAL,NULLIF((CASE main.nodes.value('Year2[1]/@xsi:nil="true"', 'bit')
                                        WHEN 1 THEN NULL
                                        ELSE main.nodes.value('Year2[1]', 'INT')
                                     END),0)) AS Year2,
            CONVERT(DECIMAL,NULLIF((CASE main.nodes.value('Year3[1]/@xsi:nil="true"', 'bit')
                                        WHEN 1 THEN NULL
                                        ELSE main.nodes.value('Year3[1]', 'INT')
                                        END),0)) AS Year3       
    FROM    @test.nodes('xml/ArrayOfURDTO/URDTO') AS main(nodes)

    Sunday, April 04, 2010 11:07 AM