Solved

XML Nodes and value

Posted on 2013-06-14
3
470 Views
Last Modified: 2013-06-15
Is there anway of getting the value if it exists on my XML Nodes output below?

SELECT x.n.value('fn:local-name(.)', 'varchar(255)') AS [Node]
     , x.n.value('fn:local-name(..)', 'varchar(255)') AS [ParentNode]
FROM @XmlDoc.nodes('//*') x(n)

Open in new window


So that if I have a node
<id>12345</id>

I could add a column for that in my output below
xx
0
Comment
Question by:lrbrister
  • 2
3 Comments
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 39249128
Yes, you would just do something like:
SELECT x.n.value('fn:local-name(.)', 'varchar(255)') AS [Node]
     , x.n.value('fn:local-name(..)', 'varchar(255)') AS [ParentNode]
    , x.n.value('./text()[1]', 'varchar(max)') AS [NodeText]
FROM @XmlDoc.nodes('//*') x(n)
;

You can adjust data type as necessary with other XML documents, and scenarios. Another way to do this is with query() where you do not have to specify the type. It returns the node/element as XML.

SELECT x.n.query('fn:local-name(.)') AS [Node]
     , x.n.query('fn:local-name(..)') AS [ParentNode]
     , x.n.query('./text()[1]') AS [NodeText]
FROM @XmlDoc.nodes('//*') x(n)
;

I hope that helps!

P.S. The /text()[1] limits the result to the text part of the node, which alleviates your getting text for the node's children as well. For example, with just '.', the org.m5.api.v1.Response node would show text '07726' versus NULL.
0
 

Author Comment

by:lrbrister
ID: 39249194
I will test in the mirning
0
 

Author Closing Comment

by:lrbrister
ID: 39250032
Perfect
Thanks
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A theme is a collection of property settings that allow you to define the look of pages and controls, and then apply the look consistently across pages in an application. Themes can be made up of a set of elements: skins, style sheets, images, and o…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

912 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now