declare @x xml
set @x = '<Root>
<row id="1"><name>Larry</name><oflw>some text</oflw></row>
<row id = "2"><name>moe</name></row>
<row id = "3"/>
</Root>'
select T.c.query(..) as result from @x.nodes('/Root/row') T(C)
select T.c.query(..) as result from @x.nodes('/Root/row') T(C)
I am getting following error.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ')'.
could any one help me correcting the error. I am new to sql .
It should one this:
select T.c.query('..') as result from @x.nodes('/Root/row') T(C)
or maybe
select T.c.query('.') as result from @x.nodes('/Root/row') T(C)
The path parameter to the query method should be a string
Not sure what you're trying to extract from your XML - but try this query - it will "take apart" all the bits of information stored in the XML and show them as separate values:
declare @x xml
set @x = '<Root>
<row id="1"><name>Larry</name><oflw>some text</oflw></row>
<row id="2"><name>moe</name></row>
<row id="3"/>
</Root>'
select
T.c.value('@id', 'int') as 'ID',
T.c.value('(name)[1]', 'varchar(25)') as result,
T.c.value('(oflw)[1]', 'varchar(25)') as 'OFlw'
from @x.nodes('/Root/row') T(C)
Output:
ID result OFlw
1 Larry some text
2 moe NULL
3 NULL NULL
<row> nodes in the <Root> parent node. You can then use those to grab individual values from each node, as if it were a table with XML values inside it