CODEWITHSUNDEEP
sqlsql-serverxml
user2078938
user2078938

Reputation: 1039

Can I query an XML tag without specifying it's parent tags in SQL Server?

I have table column that stores the outgoing XML of an API service. I need to search for all records where for example:

<personId>1</personId>

The problem is the parent tags are inconsistent, as they come from different transactions such as:

<?xml version="1.0" encoding="utf-8"?>
<order>
    <personId>1</personId>
</order>

<?xml version="1.0" encoding="utf-8"?>
<query>
    <personId>1</personId>
</query>

Typically, I'd query xml the following way:

where xmlValue.value('(order/personId)[1]', 'int') = 1

Is there a way to query the xml values without specifying the parent tags?

Upvotes: 1

Views: 61

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

If "personId" does not occur anywhere else you might go the easy way with

where xmlValue.value('(//personId)[1]', 'int') = 1

To try it out:

DECLARE @tbl TABLE(ID INT,xmlCol XML);
INSERT INTO @tbl VALUES
(1,'<?xml version="1.0" encoding="utf-8"?>
<order>
    <personId>1</personId>
</order>')
,(2,'<?xml version="1.0" encoding="utf-8"?>
<query>
    <personId>2</personId>
</query>');

If you do not know where in your XML-tree a node is situated you might use the double slash "//"

SELECT *
FROM @tbl AS tbl
WHERE xmlCol.value('(//personId)[1]','int')=2

If you know the level, but the parent name is different, you might use the Asterisk

SELECT *
FROM @tbl AS tbl
WHERE xmlCol.value('(/*/personId)[1]','int')=1

Upvotes: 3

Related Questions