CODEWITHSUNDEEP
sqlsql-serverxmlfor-xml
Volearix
Volearix

Reputation: 1593

Getting a value from my xml

I am not sure what the proper element / node / or whatever name is for returning this value but here is my example xml:

DECLARE @Input xml = '
<root>
    <myValue>
        <more></more>
        <more></more>
    </myValue>'
<root>

What I am trying to get from here is a result of "myValue". The literal string, "myValue" is what I require. So perhaps the name of the first node might be the correct way of phrasing it? Here is what I have tried, where am I going wrong?

SELECT @Input.value('local-name(/*[1])','varchar(100)')

SELECT Nodes.Name.query('local-name(.)') FROM @Input.nodes('//*[1]') As Nodes(Name)

Upvotes: 0

Views: 37

Answers (2)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67321

This question is quite unclear...

Have a look on this example to find various approaches to read this:

DECLARE @Input xml = 
'<root>
    <myValue>value 1
        <more>more 1</more>
        value 2
        <more>more 2</more>
        value 3
    </myValue>
</root>';

--Retrieve values from various positions

SELECT @Input.value('(/root/myValue/text())[2]','nvarchar(max)') AS SecondFloatingTextInMyValue
      ,@Input.value('(/root/myValue/more/text())[1]','nvarchar(max)') FirstMoreElement
      ,@Input.value('(/root/myValue/more/text())[2]','nvarchar(max)') SecondMoreElement;

--Get a derived table of <more>-elements

SELECT More.Nodes.value('(./text())[1]','nvarchar(max)') AS MoreValue
FROM @Input.nodes('/root/myValue/more') AS More(Nodes)

UPDATE

Your question

What I am trying to get from here is simple the value of "myValue"

seems to look for the value of an element, but - as your self-answer introduces - you seem to look for the elements name, where "more" is just an example...

Please read How to ask a good SQL question and How to create a MCVE

UPDATE 2

Reading the meta data of an element is done - as you found yourself - with local-name():

DECLARE @Input xml = 
'<root>
    <myValue>
        <OneValue></OneValue>
        <Another></Another>
    </myValue>
</root>';

SELECT nd.value('local-name(.)','nvarchar(max)') AS NameOfNodeBelowMyValue
FROM @Input.nodes('/root/myValue/*') AS A(nd)

Upvotes: 1

Volearix
Volearix

Reputation: 1593

I figured it out, sorry to be a bother. I am not sure the technical term of what I was looking to do here but in my case the 'myValue' node holds the name of a dbo.Table object that I need for a dynamic query.

SELECT Nodes.Name.query('local-name(.)') 
FROM @Input.nodes('/root/*[1]') As Nodes(Name)

Upvotes: 0

Related Questions