Menu
If I have:
<quotes>
<quote>
<name>john</name>
<content>something or other</content>
</quote>
<quote>
<name>mary</name>
<content>random stuff</content>
</quote>
</quotes>
How do I get a list of the element names 'name' and 'content' using T-SQL?
The best I've got so far is:
declare @xml xml
set @xml = ...
select r.value('quotes/name()[1]', 'nvarchar(20)' as ElementName
from @xml.nodes('/quotes') as records(r)
But, of course, I can't get this to work.
778
A SELECT query returns results as a rowset. You can optionally retrieve formal results of a SQL query as XML by specifying the FOR XML clause in the query. The FOR XML clause can be used in top-level queries and in subqueries. The top-level FOR XML clause can be used only in the SELECT statement.
SQL Server lets you retrieve data as XML by supporting the FOR XML clause, which can be included as part of your query. You can use the FOR XML clause in the main (outer) query as well as in subqueries. The clause supports numerous options that let you define the format of the XML data.
You should use the query() Method if you want to get a part of your XML. If you want the value from a specific node you should use value() Method. Update: If you want to shred your XML to multiple rows you use nodes() Method.
Actually, sorry, the best I've got is:
select distinct r.value('fn:local-name(.)', 'nvarchar(50)') as t
FROM
@xml.nodes('//quotes/*/*') AS records(r)
Guess I answered my own question...
75
DECLARE @xml as xml
SET @xml = '<Address><Home>LINE1</Home></Address>'
SELECT Nodes.Name.query('local-name(.)') FROM @xml.nodes('//*') As Nodes(Name)
This will give the list of all elements
32
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
