[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 479
  • Last Modified:

XML Nodes and show tree

I would like a function that I pass in XML and get the "schema" or "tree" back

For example
From this xml I wouls set @XmlDoc
ss
And get this returned
ss2
0
lrbrister
Asked:
lrbrister
1 Solution
 
Kevin CrossChief Technology OfficerCommented:
Hi.

Using your example XML:
DECLARE @XmlDoc AS XML = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<org.m5.api.v1.Response xmlns:m5="http://www.m5net.com/org/m5/data/v2/cti" xmlns:csta="http://www.ecma-international.org/standards/ecma-323/csta/ed5">
    <ErrorCount>0</ErrorCount>
    <Id>7</Id>
    <Result xsi:type="org.m5.data.v2.cti.HostedConnectObject" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
        <csta:MonitorStartResponse>
            <csta:monitorCrossRefID>726</csta:monitorCrossRefID>
        </csta:MonitorStartResponse>
    </Result>
</org.m5.api.v1.Response>
';

Open in new window


You can try something like this:
- search for all child nodes '//*'
- get local-name of the current node '.'
- get local-name of the current node's parent '..'
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


MSDN: http://msdn.microsoft.com/en-us/library/ms188282.aspx

I hope that helps!

Best regards and happy coding,

Kevin
0
 
lrbristerAuthor Commented:
Thanks.
That worked as well as EXEC sp_xml_preparedocument @handle OUTPUT, @XmlDoc
Watch for follow-up
0

Featured Post

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

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