SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Extract data from xml file
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jbphoenix
Yak Posting Veteran

68 Posts

Posted - 05/15/2013 :  14:41:18  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3568 Posts

Posted - 05/15/2013 :  15:44:18  Show Profile  Reply with Quote
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)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 05/16/2013 :  00:25:45  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000