SQL Xml for Xpath query

Question

Trying to return the value of an XML field via Xpath query.

Here is what the XML looks like in a snap shot.

<?xml version="1.0" encoding="utf-16"?>  
<ArrayOfCustomProperty xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">    
<CustomProperty>      
    <DeveloperId>Test123</DeveloperId>      
    <Key>AgreedToTerms</Key>      
    <Value>True</Value>    
</CustomProperty>    
<CustomProperty>      
    <DeveloperId>Test456</DeveloperId>      
    <Key>ValidForLoyaltyPoints</Key>      
    <Value>False</Value>    
</CustomProperty>  
</ArrayOfCustomProperty>

(sorry for the poorly-formatted XML...StackOverflows RCE is having issues with rendering it out. The ArrayOfCustomProperty is closed at the end, just not displayed for some reason)

This query works for me....

  SET @return = CAST(CAST(@xmlData AS xml).query('ArrayOfCustomProperty/CustomProperty/Key[text()=sql:variable("@key")]/../Value/text()') AS nvarchar(255))

It allows me to have a function where the parameters are @xmlData and @key for what needs to be searched. I need to have another function (or might modify this one) where I can also search the [DeveloperId] node as well, so a third parameter will be passed in as @devId. I've tried quite a few different things but nothing has worked for me yet. I'd like a query where I can get the [Value] when [DeveloperId] and [Key] are present using the same structure (if possible) to how the current Xpath query works.

Thanks in advance for any help.

Alex Kudryashev · Accepted Answer · 2019-05-20 16:53:28Z

1

You can convert xml data to a table and then process the table. Something like this.

declare @xml xml=N'<?xml version="1.0" encoding="utf-16"?>  
<ArrayOfCustomProperty xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">    
<CustomProperty>      
    <DeveloperId>Test123</DeveloperId>      
    <Key>AgreedToTerms</Key>      
    <Value>True</Value>    
</CustomProperty>    
<CustomProperty>      
    <DeveloperId>Test456</DeveloperId>      
    <Key>ValidForLoyaltyPoints</Key>      
    <Value>False</Value>    
</CustomProperty>  
</ArrayOfCustomProperty>',
@key varchar(50)

;with cte as (
select t.v.value('DeveloperId[1]','varchar(50)') DeveloperId,
    t.v.value('Key[1]','varchar(50)') [Key],
    t.v.value('Value[1]','varchar(50)') [Value]
from @xml.nodes('ArrayOfCustomProperty/CustomProperty') t(v)
)
select * from cte
where [Key] = @key

answered May 20, 2019 at 16:53

Alex Kudryashev

9,5103 gold badges31 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

JohnPete22 Over a year ago

Thanks for the reply. This does work, though I was hoping to use the inline 'query' functionality if possible. I will adapt my solution to use a table unless I see another answer. Thanks again.

Gottfried Lesigang Over a year ago

This works correctly, but can be done better. Just imagine big XMLs with a lot of <CustomProperty> elements. Your approach has to return all of them, just to through most of the result away again. You might check my answer how you can use a predicate to return only the needed element.

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

You should rather try to return as little as possible from the XML.
The best was to reduce the amount the engine has to parse to the needed minimum.
And you should avoid backward-navigation using ../. This is a well known performance killer.

Try this:

DECLARE @tbl TABLE(YourXml XML);
INSERT INTO @tbl VALUES
(N'<?xml version="1.0" encoding="utf-16"?>  
<ArrayOfCustomProperty xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">    
<CustomProperty>      
    <DeveloperId>Test123</DeveloperId>      
    <Key>AgreedToTerms</Key>      
    <Value>True</Value>    
</CustomProperty>    
<CustomProperty>      
    <DeveloperId>Test456</DeveloperId>      
    <Key>ValidForLoyaltyPoints</Key>      
    <Value>False</Value>    
</CustomProperty>  
</ArrayOfCustomProperty>');

--Your Key-variable

DECLARE @key VARCHAR(100)='ValidForLoyaltyPoints' ;

--the Query

SELECT cp.value('(DeveloperId/text())[1]','nvarchar(250)') AS DeveloperId
      ,cp.value('(Value/text())[1]','nvarchar(250)') AS Value
FROM @tbl t
CROSS APPLY t.YourXml.nodes('/ArrayOfCustomProperty/CustomProperty[(Key/text())[1]=sql:variable("@key")]') A(cp);

The XPath/XQuery within .nodes() will dive down to <CustomProperty> and will return the one with a <Key>, where the key's text is like the given filter. The found <CustomProperty> is returned. Using .value() we can read the elements below this given property.

UPDATE

It looks, as if you'd try to fetch the <Value> as a scalar value depending in <DeveloperId> and <Key>. You can use a simple predicate to query both at once:

DECLARE @key VARCHAR(100)='ValidForLoyaltyPoints' ;
DECLARE @devId VARCHAR(100)='Test456';
--the Query

SELECT t.YourXml.value(N'(/ArrayOfCustomProperty
                          /CustomProperty[(DeveloperId/text())[1]=sql:variable("@devId") 
                                      and (Key/text())[1]=sql:variable("@key")]
                          /Value/text())[1]',N'nvarchar(100)')
FROM @tbl t;

You read this as

Dive into the array
Dive deeper into CustomProperty
Find an element, where the filter matches ...
... and return its value

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered May 27, 2019 at 8:30

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

5 Comments

JohnPete22 Over a year ago

Thanks for the response. I am going to run some tests between your answer and the previous one to see the performance results. Also, thanks for the extra information.

Gottfried Lesigang Over a year ago

@JohnPete22 Great! Please be so kind and let us know the differences... In general I'd say: The more CPs you have within one <Array>, the bigger the difference will grow...

JohnPete22 Over a year ago

I'm having some issues with the function (not extremely proficient with these XML queries especially in a function). The function will take two different parameters -- @key and developerId. I need to return the [Value] where key = [Key] and developerId = [DeveloperId]. Right now this returns the DeveloperId and Value. Is there anyway to add another Cross Apply (I can't seem to figure it out) for the DeveloperID?

Gottfried Lesigang Over a year ago

@JohnPete22, That means, you pass in two parameters and want to return just the scalar value? This needs no apply at all. Just both parameters with and in the XQuery predicate within .value()

JohnPete22 Over a year ago

Yes, that is correct. I posted what I "thought" you meant with your latest comment as an Answer.

JohnPete22 · Accepted Answer · 2019-05-30 15:58:04Z

0

@Shnugo

Is this what you meant with your last comment?

DECLARE @tbl TABLE(YourXml XML);
INSERT INTO @tbl VALUES (@xmlData)

SELECT t.YourXml.value('(/ArrayOfCustomProperty/CustomProperty/Value/text())[1]','nvarchar(250)') AS Value 
FROM @tbl t
WHERE t.YourXml.value('(/ArrayOfCustomProperty/CustomProperty/Key/text())[1]','nvarchar(250)') = @key 
AND
t.YourXml.value('(/ArrayOfCustomProperty/CustomProperty/DeveloperId/text())[1]','nvarchar(250)') = @devId

answered May 30, 2019 at 15:58

JohnPete22

5233 silver badges17 bronze badges

3 Comments

JohnPete22 Over a year ago

@Shnugo not sure if the tag in the answer worked, so going to add it here. Thanks again.

Gottfried Lesigang Over a year ago

Hi John, I've added something to my answer. Neither the tag within the answer, nor the tag in the comment in this answer will trigger an alert. Because I haven't participated here.

Gottfried Lesigang Over a year ago

One more hint: If this is not meant as an answer, you should rather use the edit option of your question to add to this.

Collectives™ on Stack Overflow

SQL Xml for Xpath query

3 Answers 3

2 Comments

UPDATE

5 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

UPDATE

5 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related