XML question - 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)

XML question

Author

Topic

Luuk123
Yak Posting Veteran

52 Posts

Posted - 2013-03-27 : 14:43:01

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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-03-27 : 15:15:53

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)

Luuk123
Yak Posting Veteran

52 Posts

Posted - 2013-03-27 : 15:35:39

Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources