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.
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 xmlset @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 ClientidFROM @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.aspx3. 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 xmlset @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 ClientidFROM @xmldata.nodes('ClientData/ClientDetails')a(b) |
|
|
Luuk123
Yak Posting Veteran
52 Posts |
Posted - 2013-03-27 : 15:35:39
|
Thanks! |
|
|
|
|
|