April 19, 2016 at 4:42 am
I hate XML which is probably why I can't do this, which just makes me hate XML even more!
That personal grievance aside I need help extracting XML nodes from a database column. I don't need the values of the data contained in the nodes, I think I've done that with a bit of help from Google, but I need the actual names of the nodes in which the data is contained, if that makes any sense.
Since most things that Google is turning up is how to extract the data values it's not helped much in what I need to do.
If I have XML data that looks something like this:
<TaxAudit xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Year>20152016</Year>
<AccountNumber>1234567890</AccountNumber>
<CertificateRequested>true</CertificateRequested>
</TaxAudit>
How do I get a query to spit out Year, AccountNumber and CertificateRequested in the output?
Apologies for my ineptitude in this regard and many thanks in advance,
Christophe
Keep the rubber side down and the shiny side up.
April 19, 2016 at 4:54 am
I'm with you with regard hating XML, but does this do what you want:-
DECLARE @input XML = '<TaxAudit xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Year>20152016</Year>
<AccountNumber>1234567890</AccountNumber>
<CertificateRequested>true</CertificateRequested>
</TaxAudit>'
SELECT
NodeName = C.value('local-name(.)', 'varchar(50)')
FROM @input.nodes('/TaxAudit/*') AS T(C)
April 19, 2016 at 7:46 am
Ian,
That does just the job, thank you so much!
:w00t:
Christophe
Keep the rubber side down and the shiny side up.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply