• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 486
  • Last Modified:

XML Nodes and value

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
lrbrister
Asked:
lrbrister
  • 2
1 Solution
 
Kevin CrossChief Technology OfficerCommented:
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
 
lrbristerAuthor Commented:
I will test in the mirning
0
 
lrbristerAuthor Commented:
Perfect
Thanks
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now