How to get child of xmlelement in sql query without its name?

Question

In sql server xml column I have xml like this:

<Test>  
    <Operations>
        <Operations type="OperationSend">
            <OperationSend>                             
                <ToCompanyId>1</ToCompanyId>
                <Date>2011-05-01T00:00:00</Date>                
            </OperationSend>
        </Operations>
        <Operations type="OperationSell">
            <OperationSell>
                <ToCompanyId>33</ToCompanyId>
                <Amount>12</Amount>
            </OperationSell>
        </Operations>
        <Operations type="OperationEdit">
            <OperationEdit>
                <ToCompanyId>12</ToCompanyId>
                <Date>2011-11-01T00:00:00</Date>    
            </OperationEdit>
        </Operations>
    </Operations>
</Test>

I need to take ToCompanyId from last operation (12). I came to something like this. What should be in ??? when there can be any operation type with ToCompanyId.

select testxml.query('(/Test/Operations/Operations)[last()]/???/ToCompanyId') from dbo.MyXmlTable

I recommend that you accept the best answer -- in this specific case it is given by @Mikael Eriksson. — Dimitre Novatchev, May 15 '11 at 18:12

score 3 · Answer 1 · answered May 14 '11 at 20:46

3

You can use *

select testxml.query('(/Test/Operations/Operations)[last()]/*/ToCompanyId').value('.', 'int')
from MyXmlTable

answered May 14 '11 at 20:46

Mikael Eriksson

136,425
22
210
281

score 1 · Answer 2 · answered May 14 '11 at 19:41

Assuming that you have set your xml to be a variable named @x then this is how to get your 12.

select  x.header.value('.', 'int') 
                from @x.nodes('//Test/Operations/Operations[last()]/OperationSend/ToCompanyId')
                 as x(header)

the query would be slightly different to get from a column of a table but the XPATH would be the same.

select testxml.query('//Test/Operations/Operations[last()]/OperationSend/ToCompanyId') from dbo.MyXmlTable

score 1 · Accepted Answer · answered May 14 '11 at 20:44

1

Put node() instead of ???

node() matches all nodes of any kind

answered May 14 '11 at 20:44

krolik

5,712
1
26
30

You need to think of a more exact node-test than simply `node()`. Hint: See the answer by Mikael Eriksson. – Dimitre Novatchev May 15 '11 at 18:11

How to get child of xmlelement in sql query without its name?

3 Answers3