June 9, 2009 at 11:06 pm
I have loaded the xml below to a xml table in sql server:
when i run:
select xml_data.query('/books/book/name') from myXML
i get :
when i run:
select xml_data.value('/books[1]/book[2]/@id','CHAR(8)') from myXML
I get :2
What do i need to do to get only the values:
abc
prq
Any ideas would be appreciated.
June 9, 2009 at 11:26 pm
The XML can not be seen. Can you upload a small XML file that has the same structure as the one that you work with? In any case the value method returns only one value. If in your XML you want to get more then one value back (from the same path), you'll need to combine the node method with the value method (see the example bellow):
DECLARE @XML xml
SELECT @XML =
'
'
SELECT TeamsTable.TeamXMLCol.value ('(@Name)[1]', 'varchar(30)') as TeamName,
TeamsTable.TeamXMLCol.value ('(@Leader)[1]', 'varchar(20)') as LeaderName
FROM @xml.nodes('/root/Company/Team') TeamsTable (TeamXMLCol)
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 10, 2009 at 12:51 am
Its
abc
pqr
Thanks
June 10, 2009 at 1:35 am
declare @xml xml
set @xml =
'
abc
pqr
'
select tab.col.value('.[1]','varchar(30)')
from @xml.nodes('BOOKS/BOOK/NAME') tab (col)
Adi
EDIT - In my original code the values in the ID tags were surrounded by quotation marks ("), but it got deleted in the code window. Before running the coded be sure to add the quotation marks. If you won't, you will get an error.
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 10, 2009 at 1:57 am
Thanks Adi
I'm quite confused.
I have a table called myXML.with a column name called xml_data
and in this column i have:
abc
pqr
but when i run
select myXML.xm_data.value('name[1]','varchar(30)')
from xm_data.nodes('books/book') myXML (xm_data)
I get error
Msg 207, Level 16, State 1, Line 3
Invalid column name 'xm_data'.
Msg 9506, Level 16, State 1, Line 2
The XMLDT method 'nodes' can only be invoked on columns of type xml.
No idea why its won't work
Thanks
June 10, 2009 at 2:06 am
Since the node method is creating a table, when your original data comes from a column in a table and not with a variable, you need to do a cross join between the table and the nodes method. Check out this example:
use tempdb
go
create table myXML (id int identity(1,1) primary key,
xm_data xml)
go
insert into MyXML (xm_data)
select
'
abc
pqr
'
go
select tab.col.value('.[1]','varchar(30)')
from myXML cross apply xm_data.nodes('BOOKS/BOOK/NAME') tab (col)
go
drop table myXML
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 10, 2009 at 2:27 am
finally got it:
So the tab.col were just aliases
select tab.col.value('@id[1]','varchar(30)') as name
from myXML cross apply xml_data.nodes('books/book') tab (col)
sorry for this
select myXML.xml_data.value('name[1]','varchar(30)')
from myXML cross apply xml_data.nodes('books/book/name') myXML (xml_data)
I tried this and still getting error
Msg 1012, Level 16, State 1, Line 2
The correlation name 'myXML' has the same exposed name as table 'myXML'.
if understood your post
select tab.col.value('.[1]','varchar(30)')
from myXML cross apply xm_data.nodes('BOOKS/BOOK/NAME') tab (col)
is
tab- the table name
col - the column name
June 10, 2009 at 3:09 am
I think that you got it. I'm sorry but I thought that you know what nodes method is doing. The method is returning a table that has one column in it. The first name that comes after you invoke the method is the table's name. The second name is the column name. You need to query the column that was created by the noeds method. Since the nodes method returns XML in the table, you need to use Xquery on that column. It might be easier to understand if you'll also show at the XML that nodes method returns. I've made a small modification to my code so it will show you also the XML that was returned by the nodes method (the method query with the input ('.') returns the XML that was queried without any modification).
use tempdb
go
create table myXML (id int identity(1,1) primary key,
xm_data xml)
go
insert into MyXML (xm_data)
select
'
abc
pqr
'
go
select tab.col.value('.[1]','varchar(30)'),
tab.col.query('.')
from myXML cross apply xm_data.nodes('BOOKS/BOOK/NAME') tab (col)
go
drop table myXML
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply