SQL Server: How to handle empty data found in xml node during select data from xml

Question

Suppose my xml is stored in variable and I want to display xml data in tabular format. so this query i fired.

Declare @xml xml  

set @Xml='<?xml version="1.0" encoding="utf-8"?>
<TickerBrokerStandardDateLineitem>
  <Ticker />
  <TickerID />
  <TickerBrokerStandardDateLineitemValues>
    <TickerBrokerStandardDateLineitemValue>
      <TabName>xxxxxxxx</TabName>
      <StandardDate>2010 FY</StandardDate>
      <XFundCode>xxxxx</XFundCode>
      <BRTab></BRTab>
      <BRLineItem></BRLineItem>
      <StandardLineItem>aaaaa</StandardLineItem>
      <StandardValue>1608.7</StandardValue>
      <ActualProvidedByCompany>NO</ActualProvidedByCompany>
    </TickerBrokerStandardDateLineitemValue>
    <TickerBrokerStandardDateLineitemValue>
      <TabName>qqqqqqq</TabName>
      <StandardDate>3Q 2018</StandardDate>
      <XFundCode>RD_015</XFundCode>
      <BRTab></BRTab>
      <BRLineItem></BRLineItem>
      <StandardLineItem>ssssss</StandardLineItem>
      <StandardValue></StandardValue>
      <ActualProvidedByCompany>YES</ActualProvidedByCompany>
    </TickerBrokerStandardDateLineitemValue>
  </TickerBrokerStandardDateLineitemValues>
</TickerBrokerStandardDateLineitem>'

SELECT 
    x.Rec.query('./TabName').value('.', 'VARCHAR(MAX)') AS 'TabName',
    x.Rec.query('./StandardDate').value('.', 'VARCHAR(MAX)') AS 'StandardDate',
    x.Rec.query('./XFundCode').value('.', 'VARCHAR(MAX)') AS 'XFundCode',
    x.Rec.query('./BRTab').value('.', 'VARCHAR(MAX)') AS 'BRTab',
    x.Rec.query('./BRLineItem').value('.', 'VARCHAR(MAX)') AS 'BRLineItem',
    x.Rec.query('./StandardLineItem').value('.', 'VARCHAR(MAX)') AS 'StandardLineItem',
    x.Rec.query('./StandardValue').value('.', 'DECIMAL (18, 2)') AS 'StandardValue',
    x.Rec.query('./ActualProvidedByCompany').value('.', 'VARCHAR(MAX)') AS 'ActualProvidedByCompany'   
FROM @xml.nodes('/TickerBrokerStandardDateLineitem/TickerBrokerStandardDateLineitemValues/TickerBrokerStandardDateLineitemValue') as x(Rec)

As you can see the xml attribute <StandardValue></StandardValue> has no value in xml, but it is declared as decimal. So what kind of change do I need to add in my code so, that when <StandardValue></StandardValue> has no data then query will return 0.00 as the default value?

What kind of change should be there in this line x.Rec.query('./StandardValue').value('.', 'DECIMAL (18, 2)') AS 'StandardValue',

I tried this nullif(0.0,x.Rec.query('./StandardValue').value('.', 'DECIMAL (18, 2)')) AS 'StandardValue', but no luck and Error I got Error converting data type nvarchar to numeric.

Please guide me. thanks

Thom A · Accepted Answer · 2018-11-29 12:40:47Z

2

<StandardValue></StandardValue> (or <StandardValue\>) isn't the same as the node StandardValue not appearing in your XML. This means that, instead, the StandardValue node has the varchar value ''. If a node doesn't have a value (NULL) then ideally don't include it in your data.

The varchar value '' can't be converted to a numerical data type (try CONVERT(int,'') and CONVERT(decimal(10,2),'')), so you'll need to return the value as a varchar. Try instead using:

ISNULL(TRY_CONVERT(decimal(10,2),x.Rec.query('./StandardValue/text()').value('.', 'varchar(11)')),0) AS StandardValue,

Notice that I've also added text(), as this is actually faster. I would suggest adding this to your other columns as well; especially if this query is against a (large) dataset rather than a variable.

edited Nov 29, 2018 at 12:40

answered Nov 29, 2018 at 11:47

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Server: How to handle empty data found in xml node during select data from xml

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related