-1

This is the XML table I need to query. enter image description here And I need to query the 52324672-bd54-44cf-b836-869d39d64bd8 from value tag. I tried to query like this enter image description here But it is not working as the issue looks from the i:type="a:string" and xmlns:i="http://www.w3.org/2001/XMLSchema-instance".

1
  • When you say it is "not working", what exactly does that mean? You're getting an error message? You're getting an unexpected result?
    – Craig
    Commented Oct 19, 2023 at 21:07

1 Answer 1

1

I can't believe you added a screenshot of the XML text. Next time, please copy and paste the XML directly. This will allow others to help you more effectively with your specific XML issue.

But it is not working as the issue looks from the i:type="a:string" and xmlns:i="http://www.w3.org/2001/XMLSchema-instance".

From the second screenshot, you have a different XML structure that does not include the namespace xmlns:i="http://www.w3.org/2001/XMLSchema-instance". This namespace is required because there's a namespace on the child element value. To retrieve the value 523.., here's an example xquery you can use:

DECLARE @xml XML = '
<ArrayOfKeyValuePairOfstringanyType xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
    <KeyValuePairOfstringanyType subsheetid="feb" type="xx" published="True">
        <key>3d65</key>
        <value xmlns:a="http://www.w3.org/2001/XMLSchema" i:type="a:string">523</value>
    </KeyValuePairOfstringanyType>
</ArrayOfKeyValuePairOfstringanyType>
';

SELECT 
    c.value('(value/text())[1]', 'varchar(100)') AS [Value]
FROM 
    @xml.nodes('/ArrayOfKeyValuePairOfstringanyType/KeyValuePairOfstringanyType[1]') AS t(c);

Note: The above script will return the value child element of KeyValuePairOfstringanyType due to the presence of the predicate [1]. If you have multiple KeyValuePairOfstringanyType parent elements and want to return all value for each KeyValuePairOfstringanyType, you'll need to remove the [1]. Basically, this is just one example. There are so many ways to shred XML.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.