XML Data retrieval

  • Please help me to display data instead of null,

    My CODE:

    DECLARE @tempTable TABLE

    (

    countryCode NVARCHAR(10),

    zoneName NVARCHAR(50),

    abbreviation NVARCHAR(10),

    gmtOffset INT,

    dst INT

    )

    DECLARE @xml XML;

    SET @xml =

    <?xml version="1.0" encoding="UTF-8"?>

    <result>

    <status>OK</status>

    <message></message>

    <countryCode>US</countryCode>

    <zoneName>America/Los_Angeles</zoneName>

    <abbreviation>PDT</abbreviation>

    <gmtOffset>-25200</gmtOffset>

    <dst>1</dst>

    <timestamp>1430141082</timestamp>

    </result>

    INSERT INTO @tempTable

    SELECT

    Tbl.Col.value('@countryCode', 'NVARCHAR(5)') AS countryCode,

    Tbl.Col.value('@zoneName', 'NVARCHAR(50)') AS zoneName,

    Tbl.Col.value('@abbreviation', 'NVARCHAR(6)') AS abbreviation,

    Tbl.Col.value('@gmtOffset', 'INT') AS gmtOffset,

    Tbl.Col.value('@dst', 'INT') AS dst

    FROM @xml.nodes('/result') Tbl(Col)

    --See the table

    SELECT * FROM @tempTable

    O/P:

    countryCode, zoneName, abbreviation,gmtOffset,dst

    NULL, NULL, NULL, NULL , NULL

  • The @ sign is used for referring to XML attributes. You are looking for the value of the Element.

    Below is the code that will get you what you are looking for:

    DECLARE @tempTable TABLE

    (

    countryCode NVARCHAR(10),

    zoneName NVARCHAR(50),

    abbreviation NVARCHAR(10),

    gmtOffset INT,

    dst INT

    )

    DECLARE @xml XML;

    SET @xml =

    '<?xml version="1.0" encoding="UTF-8"?>

    <result resultID="200">

    <status>OK</status>

    <message></message>

    <countryCode>US</countryCode>

    <zoneName>America/Los_Angeles</zoneName>

    <abbreviation>PDT</abbreviation>

    <gmtOffset>-25200</gmtOffset>

    <dst>1</dst>

    <timestamp>1430141082</timestamp>

    </result>'

    SELECT

    Tbl.Col.value('(countryCode)[1]', 'NVARCHAR(5)') AS countryCode,

    Tbl.Col.value('(zoneName)[1]', 'NVARCHAR(50)') AS zoneName,

    Tbl.Col.value('(abbreviation)[1]', 'NVARCHAR(6)') AS abbreviation,

    Tbl.Col.value('(gmtOffset)[1]', 'INT') AS gmtOffset,

    Tbl.Col.value('(dst)[1]', 'INT') AS dst

    FROM @xml.nodes('/result') Tbl(Col);

    Notice that I added an attribute named resultID to your sample XML: <result resultID="200">

    You would retrieve that value using the @ sign like so:

    Tbl.Col.value('@resultID','INT') AS resultID

    BTW, you don't need to refer to the table name/alias when retrieving XML values.

    One last thing. When retrieving text, it's best to refer to a text() node. It improves performance. This will get you the results you want faster than my previous solution with cleaner code:

    SELECT

    Col.value('(countryCode/text())[1]', 'NVARCHAR(5)') AS countryCode,

    Col.value('(zoneName/text())[1]', 'NVARCHAR(50)') AS zoneName,

    Col.value('(abbreviation/text())[1]', 'NVARCHAR(6)') AS abbreviation,

    Col.value('(gmtOffset/text())[1]', 'INT') AS gmtOffset,

    Col.value('(dst/text())[1]', 'INT') AS dst,

    Col.value('@resultID','INT') AS resultID

    FROM @xml.nodes('/result') Tbl(Col);

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • Quick thought on optimization, as Alan mentioned, the text() method is faster.

    😎

    Same XML data sample as before

    USE tempdb;

    GO

    SET NOCOUNT ON;

    DECLARE @xml XML;

    SET @xml =

    '<?xml version="1.0" encoding="UTF-8"?>

    <result resultID="200">

    <status>OK</status>

    <message></message>

    <countryCode>US</countryCode>

    <zoneName>America/Los_Angeles</zoneName>

    <abbreviation>PDT</abbreviation>

    <gmtOffset>-25200</gmtOffset>

    <dst>1</dst>

    <timestamp>1430141082</timestamp>

    </result>'

    Looking at the first nodes query without the text method

    SELECT

    Tbl.Col.value('(countryCode)[1]' , 'NVARCHAR(5)' ) AS countryCode,

    Tbl.Col.value('(zoneName)[1]' , 'NVARCHAR(50)') AS zoneName,

    Tbl.Col.value('(abbreviation)[1]' , 'NVARCHAR(6)' ) AS abbreviation,

    Tbl.Col.value('(gmtOffset)[1]' , 'INT' ) AS gmtOffset,

    Tbl.Col.value('(dst)[1]' , 'INT' ) AS dst

    FROM @xml.nodes('result') Tbl(Col);

    Execution Plan 1

    we see two XML Reader functon calls for each value joined with a nested loop. Changing the query to use the text() method removes all but one of the extra functon calls. This reduces the cost of the query by approximately 75%.

    SELECT

    Col.value('(countryCode/text())[1]' , 'NVARCHAR(5)' ) AS countryCode,

    Col.value('(zoneName/text())[1]' , 'NVARCHAR(50)') AS zoneName,

    Col.value('(abbreviation/text())[1]' , 'NVARCHAR(6)' ) AS abbreviation,

    Col.value('(gmtOffset/text())[1]' , 'INT' ) AS gmtOffset,

    Col.value('(dst/text())[1]' , 'INT' ) AS dst,

    Col.value('(@resultID)' , 'INT' ) AS resultID

    FROM @xml.nodes('/result') Tbl(Col);

    Execution Plan 2

    Looking at the second execution plan we see there is still some room for improvements, the retrieval of the attribute @resultID is close to 50% of the cost, there is still an extra XML Reader function call and each value has a Filter operator which isn't really doing anything.

    The last version of the query bypasses the nodes() method by directly calling the value() method on the XML variable.

    SELECT

    @xml.value('(/result/countryCode/text())[1]' , 'NVARCHAR(5)' ) AS countryCode,

    @xml.value('(/result/zoneName/text())[1]' , 'NVARCHAR(50)') AS zoneName,

    @xml.value('(/result/abbreviation/text())[1]' , 'NVARCHAR(6)' ) AS abbreviation,

    @xml.value('(/result/gmtOffset/text())[1]' , 'INT' ) AS gmtOffset,

    @xml.value('(/result/dst/text())[1]' , 'INT' ) AS dst,

    @xml.value('(/result/@resultID)[1]' , 'INT' ) AS resultID

    ;

    Execution Plan 3

    Not much more to do here 😉

    Comparing the costs of the three queries we see that the improvements are substantial, the third being close to 1% of the first.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply