Multi tool use

Querying a XML column in SQL Server [closed]

I wish to query a XML column in SQL Server. Following is the XML structure:

<MessageEnvelope xmlns="http://schemas.xyz.com/messagebus/messageenvelope/1.0" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
<CustomField1/>
<CustomField2 />
<CustomField3 />
<MessagePayload>
<Counterpart xmlns="http://schemas.xyz.com/messagebus/saxotrading/counterpart/1.1">
<CounterpartID>111</CounterpartID>
<TaxCountry>65</TaxCountry>
<CustomerIdTypeCode>D</CustomerIdTypeCode>
</Counterpart>
<Counterpart xmlns="http://schemas.xyz.com/messagebus/saxotrading/counterpart/1.1">
<CounterpartID>112</CounterpartID>
<TaxCountry>5</TaxCountry>
<CustomerIdTypeCode>N</CustomerIdTypeCode>
</Counterpart>
<Counterpart xmlns="http://schemas.xyz.com/messagebus/saxotrading/counterpart/1.1">
<CounterpartID>113</CounterpartID>
<TaxCountry>65</TaxCountry>
<CustomerIdTypeCode>D</CustomerIdTypeCode>
</Counterpart>
<Counterpart xmlns="http://schemas.xyz.com/messagebus/saxotrading/counterpart/1.1">
<CounterpartID>114</CounterpartID>
<TaxCountry>15</TaxCountry>
<CustomerIdTypeCode>N</CustomerIdTypeCode>
</Counterpart>
<Counterpart xmlns="http://schemas.xyz.com/messagebus/saxotrading/counterpart/1.1">
<CounterpartID>115</CounterpartID>
<TaxCountry>65</TaxCountry>
<CustomerIdTypeCode>D</CustomerIdTypeCode>
</Counterpart>
<Counterpart xmlns="http://schemas.xyz.com/messagebus/saxotrading/counterpart/1.1">
<CounterpartID>116</CounterpartID>
<TaxCountry>51</TaxCountry>
<CustomerIdTypeCode>N</CustomerIdTypeCode>
</Counterpart>
</MessagePayload>
</MessageEnvelope>


My desired filter is TaxCountry =65 and I wish to get the counterpartId of my matched filter.

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

1 Answer
1

You need to pay attentiton to the two XML namespaces in your XML document - but otherwise, it's pretty straightforward:

-- define the two namespaces in question - one for the root element and its subordinates,
-- the second one for the <Counterpart> nodes and its children
;WITH XMLNAMESPACES('http://schemas.xyz.com/messagebus/messageenvelope/1.0' AS root,
'http://schemas.xyz.com/messagebus/saxotrading/counterpart/1.1' AS ns)
SELECT
xc.value('(ns:CounterpartID)[1]', 'int')
FROM
@input.nodes('/root:MessageEnvelope/root:MessagePayload/ns:Counterpart') AS xt(xc)
WHERE
xc.value('(ns:TaxCountry)[1]', 'int') = 65