Extract data from xml file - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Extract data from xml file

Author

Topic

jbphoenix
Yak Posting Veteran

68 Posts

Posted - 2013-05-15 : 14:41:18

I need to extract data from my xml file and us it to another query. This is the query I'm using and it returns 1 row of nulls.

SELECT DISTINCT
T.C.value('code[1]', 'varchar(2)') as Country,
T.C.value('hasState[2]', 'int') as St,
T.C.value('hasPostalCode[3]','int') as Postal
FROM ( SELECT CONVERT(XML, BulkColumn ) AS Data
from openrowset(bulk 'C:\Data\Postal.xml', single_blob) as I
) as x
cross apply data.nodes('Countries/Country') as T(C)

and this is my xml -
<Countries xmlns="">
<Country code="GB" hasStates="0" hasPostal="1" ModDate="2010-05-26" />
<Country code="UK" hasStates="0" hasPostal="1" ModDate="2010-05-26" />
<Country code="PE" hasStates="0" hasPostal="0" ModDate="2010-05-26" />
<Country code="US" hasStates="1" hasPostal="1" ModDate="2010-05-26" />
<Country code="AU" hasStates="1" hasPostal="1" ModDate="2010-07-22" />
<Country code="CAN" hasStates="1" hasPostal="1" ModDate="2010-07-22" />
</Countries>

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-15 : 15:44:18

I see several spelling errors in the column names (e.g., hasStates in the XML fragment, but you are quering for hasState). Careful about the case as well - XML is case sensitive even if your server/database has case insensitive collation. Also, I didn't follow the logic of getting the first value of code, second value of hasStates etc. (code[1] vs hasStates[2] etc.) In any case, try the following and see if it is any better.

SELECT DISTINCT 
T.C.value('./@code', 'varchar(2)') as Country,
T.C.value('./@hasStates', 'int') as St,
T.C.value('./@hasPostal','int') as Postal
FROM ( SELECT CONVERT(XML, BulkColumn ) AS Data 
from openrowset(bulk 'C:\Data\Postal.xml', single_blob) as I
) as x
cross apply data.nodes('Countries/Country') as T(C)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-16 : 00:25:45

your codes are all unique values so distinct doesnt make any sense in your select. You may dispense with that.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources