CODEWITHSUNDEEP
xmlt-sqlxpathxqueryxml-namespaces
Globe
Globe

Reputation: 292

Simple xml.nodes t-sql query does not work

Falks, here is xml:

declare @xml xml =
Cast('<asset_market_data_response xmlns="http://schemas.bcs.ru/marketing_data_service/in/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <system_block xmlns="http://schemas.bcs.ru/is/clipboard/">
    <originator>Bloomberg</originator>
    <mean_for>IS.Clipboard</mean_for>
    <document_id>e7aa2033-0a53-4390-a09b-504673ea54bb</document_id>
    <event>New</event>
    <event_time>2017-11-23T13:35:49.171696+03:00</event_time>
  </system_block>
  <common_block>
    <request_id>603bc973-39d0-e711-9417-984be16869ec</request_id>
  </common_block>
  <body>
    <date_request>2017-11-22T00:00:00</date_request>
    <data_request_code>bloomberg_bond</data_request_code>
    <data_source_code>Bloomberg</data_source_code>
    <data_version xsi:nil="true" />
    <error_message_source xsi:nil="true" />
    <asset_list>
      <asset>
        <common>
          <identifier>XS0114288789</identifier>
          <trading_floor_code xsi:nil="true" />
          <trading_floor_section_code>Equity</trading_floor_section_code>
          <trading_floor_board_code xsi:nil="true" />
        </common>
        <data_list>
          <data>
            <name>CRNCY</name>
            <value>USD</value>
          </data>
          <data>
            <name>PAR_AMT</name>
            <value>.475000000</value>
          </data>
        </data_list>
      </asset>
      <asset>
        <common>
          <identifier>CH0385518086</identifier>
          <trading_floor_code xsi:nil="true" />
          <trading_floor_section_code>Equity</trading_floor_section_code>
          <trading_floor_board_code xsi:nil="true" />
        </common>
        <data_list>
          <data>
            <name>CRNCY</name>
            <value>CHF</value>
          </data>
          <data>
            <name>PAR_AMT</name>
            <value>5000.000000000</value>
          </data>
        </data_list>
      </asset>
    </asset_list>
  </body>
</asset_market_data_response>' as xml)

Here is small select over it to grab identifier column only:

select c.value('@identifier', 'varchar(50)') as identifier
from @xml.nodes('asset_market_data_response/body/asset_list/asset/common') t(c)

But it gives no output (col name 'identifier' & no rows). What I need is:

enter image description here

What's wrong with my select? It's so simple, I cannot get what goes wrong. May be, there is some problem with xml itself?

Upvotes: 0

Views: 517

Answers (2)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

No, you should not use string methods to repair your XML, this is perfectly okay!

You just have to declare the namespace involved:

WITH XMLNAMESPACES(DEFAULT 'http://schemas.bcs.ru/marketing_data_service/in/')
select c.value('(identifier/text())[1]', 'varchar(50)') as identifier
from @xml.nodes('asset_market_data_response/body/asset_list/asset/common') t(c);

Upvotes: 2

Globe
Globe

Reputation: 292

Found problem in xml itself. Removed nodes ending " />" + xml schema references:

set @chr =  Replace(
            Replace(
            Replace(
            Replace(
            Replace(
            Replace(
            Replace(@chr, ' xmlns="http://schemas.bcs.ru/marketing_data_service/in/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"', ''),
                          ' xmlns="http://schemas.bcs.ru/is/clipboard/"', ''),
                          '<data_version xsi:nil="true" />', ''),
                          '<error_message_source xsi:nil="true" />', ''),
                          '<trading_floor_code xsi:nil="true" />', ''),
                          '<trading_floor_board_code xsi:nil="true" />', ''),
                          '<value xsi:nil="true" />', '<value></value>')

After this operatin resulting xml to parce is:

<asset_market_data_response>
  <system_block>
    <originator>Bloomberg</originator>
    <mean_for>IS.Clipboard</mean_for>
    <document_id>e7aa2033-0a53-4390-a09b-504673ea54bb</document_id>
    <event>New</event>
    <event_time>2017-11-23T13:35:49.171696+03:00</event_time>
  </system_block>
  <common_block>
    <request_id>603bc973-39d0-e711-9417-984be16869ec</request_id>
  </common_block>
  <body>
    <date_request>2017-11-22T00:00:00</date_request>
    <data_request_code>bloomberg_bond</data_request_code>
    <data_source_code>Bloomberg</data_source_code>
    <asset_list>
      <asset>
        <common>
          <identifier>XS0114288789</identifier>
          <trading_floor_section_code>Equity</trading_floor_section_code>
        </common>
        <data_list>
          <data>
            <name>CRNCY</name>
            <value>USD</value>
          </data>
          <data>
            <name>PAR_AMT</name>
            <value>.475000000</value>
          </data>
        </data_list>
      </asset>
      <asset>
        <common>
          <identifier>CH0385518086</identifier>
          <trading_floor_section_code>Equity</trading_floor_section_code>
        </common>
        <data_list>
          <data>
            <name>CRNCY</name>
            <value>CHF</value>
          </data>
          <data>
            <name>PAR_AMT</name>
            <value>5000.000000000</value>
          </data>
        </data_list>
      </asset>
    </asset_list>
  </body>
</asset_market_data_response>

And operable code to give desired result is:

select Tab_ass.Col_ass.value('identifier[1]', 'varchar(50)') as identifier
from @xml.nodes('asset_market_data_response/body/asset_list') as Tab(Col)      
cross apply Tab.Col.nodes('asset/common') Tab_ass(Col_ass)

This works too:

select t.c.value('identifier[1]', 'varchar(50)') as identifier
from @xml.nodes('asset_market_data_response/body/asset_list/asset/common') t(c)

And this too:

select t.c.query('./identifier').value('.', 'varchar(50)') as identifier
from @xml.nodes('asset_market_data_response/body/asset_list/asset/common') t(c)

Upvotes: -1

Related Questions