XML query returning NULL

HB_Air · January 17, 2023, 10:58am

Hi team
Hoping you can help with the following. Hopefully in the correct section

I am trying to read an xml message and return the value in a tag.
My query below returns NULL in the identifier field
Attached screen shot of xml file, tag highlighted in yellow
code below

My expectation is to see CNYFIX= in the Identifier column.

I would guess a simple fix.
xml query

;WITH XMLNAMESPACES 
('http://www.****/ReportRequest.xsd' AS ns)
select requestTime, extractId, extractKey, requestFilename, requestContent, priceSource, 
--requestContent.value ('(/ns:ReportRequest/ns:Identifier)[1]','varchar(20)') AS Identifier
requestContent.value ('(Identifier)[1]','VARCHAR(20)') AS Identifier
from DataScope.Extract
where requestTime >=( select cast(getdate() as date) ) 
and responseContent is not null

Thanking you in advance

harishgg1 · January 17, 2023, 11:22am

hi

to try to fix it
could you please post the XML File ?

is it a XML column in table ?

HB_Air · January 17, 2023, 11:25am

I am afraid my work security will not allow it, hence the screen shot
The xml is a column called "requestContent" in the table datascope.extract

Best I can do is

<ReportRequest xmlns="http://www**/ReportRequest.xsd">
  <InputList>
    <InputListAction>Replace</InputListAction>
    <Name>Adhoc Price Request c3989d0e-df3b-4009-a521-4e1e6992c4c2</Name>
    <Instrument>
      <IdentifierType>RIC</IdentifierType>
      <Identifier>JISDOR=</Identifier>
    </Instrument>
  </InputList>
  <ReportTemplate>
    <ReportAction>Replace</ReportAction>
    <Name>Adhoc Price Response c3989d0e-df3b-4009-a521-4e1e6992c4c2</Name>
    <EODPricing>
      <OutputFormat>XML</OutputFormat>
      <Compression>None</Compression>
      <Body>
        <DataField>
          <Name>RIC</Name>
          <DataFieldType>Text</DataFieldType>
          <Width>15</Width>
        </DataField>
        <DataField>
          <Name>Universal Close Price</Name>
          <DataFieldType>Numeric</DataFieldType>
          <DecimalPlaces>6</DecimalPlaces>
          <IntegerPlaces>10</IntegerPlaces>
          <Width>15</Width>
        </DataField>
        <DataField>
          <Name>Universal Close Price Date</Name>
          <DataFieldType>Date</DataFieldType>
          <DateFormat>ddMMyyyy</DateFormat>
        </DataField>
      </Body>
    </EODPricing>
  </ReportTemplate>
  <Schedule>
    <ScheduleAction>Replace</ScheduleAction>
    <Name>Adhoc_Request_c3989d0e-df3b-4009-a521-4e1e6992c4c2</Name>
    <EODPricingSchedule>
      <InputListName>Adhoc Price Request c3989d0e-df3b-4009-a521-4e1e6992c4c2</InputListName>
      <ReportTemplateName>Adhoc Price Response c3989d0e-df3b-4009-a521-4e1e6992c4c2</ReportTemplateName>
      <OutputFileName>NdfFix-Response-c3989d0e-df3b-4009-a521-4e1e6992c4c2_Live.xml</OutputFileName>
      <ScheduleImmediate>
        <Type>LastUpdate</Type>
      </ScheduleImmediate>
    </EODPricingSchedule>
  </Schedule>
</ReportRequest>

harishgg1 · January 17, 2023, 12:37pm

hi hope this helps

DECLARE @xml XML = N'<ReportRequest xmlns="http://www**/ReportRequest.xsd">
  <InputList>
    <InputListAction>Replace</InputListAction>
    <Name>Adhoc Price Request c3989d0e-df3b-4009-a521-4e1e6992c4c2</Name>
    <Instrument>
      <IdentifierType>RIC</IdentifierType>
      <Identifier>JISDOR=</Identifier>
    </Instrument>
  </InputList>
  <ReportTemplate>
    <ReportAction>Replace</ReportAction>
    <Name>Adhoc Price Response c3989d0e-df3b-4009-a521-4e1e6992c4c2</Name>
    <EODPricing>
      <OutputFormat>XML</OutputFormat>
      <Compression>None</Compression>
      <Body>
        <DataField>
          <Name>RIC</Name>
          <DataFieldType>Text</DataFieldType>
          <Width>15</Width>
        </DataField>
        <DataField>
          <Name>Universal Close Price</Name>
          <DataFieldType>Numeric</DataFieldType>
          <DecimalPlaces>6</DecimalPlaces>
          <IntegerPlaces>10</IntegerPlaces>
          <Width>15</Width>
        </DataField>
        <DataField>
          <Name>Universal Close Price Date</Name>
          <DataFieldType>Date</DataFieldType>
          <DateFormat>ddMMyyyy</DateFormat>
        </DataField>
      </Body>
    </EODPricing>
  </ReportTemplate>
  <Schedule>
    <ScheduleAction>Replace</ScheduleAction>
    <Name>Adhoc_Request_c3989d0e-df3b-4009-a521-4e1e6992c4c2</Name>
    <EODPricingSchedule>
      <InputListName>Adhoc Price Request c3989d0e-df3b-4009-a521-4e1e6992c4c2</InputListName>
      <ReportTemplateName>Adhoc Price Response c3989d0e-df3b-4009-a521-4e1e6992c4c2</ReportTemplateName>
      <OutputFileName>NdfFix-Response-c3989d0e-df3b-4009-a521-4e1e6992c4c2_Live.xml</OutputFileName>
      <ScheduleImmediate>
        <Type>LastUpdate</Type>
      </ScheduleImmediate>
    </EODPricingSchedule>
  </Schedule>
</ReportRequest>'

;WITH XMLNAMESPACES('http://www**/ReportRequest.xsd' as ns)
SELECT 
     x.s.value('(./ns:Identifier)[1]', 'varchar(50)') AS [Identifier]
FROM 
    @xml.nodes('/ns:ReportRequest/ns:InputList/ns:Instrument') AS x(s);

HB_Air · January 17, 2023, 12:59pm

Hi harishagg1Preformatted text

Thank you, however I am not sure how to combine your "From" comment into the following code.


;WITH XMLNAMESPACES 
('http://www.refinitiv.com/Datascope/ReportRequest.xsd' AS ns)
select requestTime, extractId, extractKey, requestFilename, requestContent, priceSource, 
requestContent.value ('(./ns:Identifier)[1]','VARCHAR(20)') AS Identifier

**from DataScope.Extract de**

where requestTime >=( select cast(getdate() as date) ) 
and responseContent is  null

as a reminder column "requestComment/" is the xml data

Could you also explain where inputlist/Instrument comes from please

('/ns:ReportRequest/ns:InputList/ns:Instrument') AS x(s);

Thanks

HB_Air · January 17, 2023, 1:03pm

Attached, copy of table output