XML Data retrieval

Question

Post reply

XML Data retrieval

arun.nallore

SSC Enthusiast

Points: 161
More actions
April 27, 2015 at 3:44 pm

#97460

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

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

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

Alan Burstein SSC Guru Points: 61141 More actions · Answer 1

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

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 2

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.