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)
 XML question
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Luuk123
Starting Member

45 Posts

Posted - 03/27/2013 :  14:43:01  Show Profile  Reply with Quote
Hi all,

I'm trying to extract data from an XML file into my SQL table.

When I use the following code, ssms returns nothing.

DECLARE @xmldata xml
set @xmldata = '<ClientData xmlns="http://example.org/ClientData.xsd">
				  <ClientDetails>
				    <ClientID>123345567</ClientID>
				    <ClientCreatedDate>2013-02-28T15:55:28.887+01:00</ClientCreatedDate>
				  </ClientDetails>
				</ClientData>
				'

SELECT a.b.value('./ClientID[1]/@id','int') AS Clientid
FROM @xmldata.nodes('/ClientDetails')a(b)


Can somebody tell me what i'm doing wrong?

Thanks!

James K
Flowing Fount of Yak Knowledge

3327 Posts

Posted - 03/27/2013 :  15:15:53  Show Profile  Reply with Quote
A few of things:
1. You don't have an attribute named id, so you should not have the /@id in the query.
2. You have to specify the namespace (or specify the namespace as default - see below) http://msdn.microsoft.com/en-us/library/ms177400.aspx
3. You have to navigate to the ClientDetails node (or specify any nested child using the "//") Look for section 3.2 in the W3C spec here http://www.w3.org/TR/xpath20/
DECLARE @xmldata xml
set @xmldata = '<ClientData xmlns="http://example.org/ClientData.xsd">
				  <ClientDetails>
				    <ClientID>123345567</ClientID>
				    <ClientCreatedDate>2013-02-28T15:55:28.887+01:00</ClientCreatedDate>
				  </ClientDetails>
				</ClientData>
				'
;with xmlnamespaces (default 'http://example.org/ClientData.xsd')
SELECT a.b.value('./ClientID[1]','int') AS Clientid
FROM @xmldata.nodes('ClientData/ClientDetails')a(b)
Go to Top of Page

Luuk123
Starting Member

45 Posts

Posted - 03/27/2013 :  15:35:39  Show Profile  Reply with Quote
Thanks!
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.06 seconds. Powered By: Snitz Forums 2000