CODEWITHSUNDEEP
sqlsql-serverxmlxpath
JohnPete22
JohnPete22

Reputation: 523

SQL Xml for Xpath query

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.

Upvotes: 2

Views: 178

Answers (3)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

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

Upvotes: 1

JohnPete22
JohnPete22

Reputation: 523

@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

Upvotes: 0

Alex Kudryashev
Alex Kudryashev

Reputation: 9460

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

Upvotes: 1

Related Questions