Sunday, 22 November 2015

XML : Escaping non-existing field during XML Parsing

I am new to XML-Data parse in SQL. Currently I am using Microsoft SQL Server Management Studio-2012.

  1. I store XML Data into Table "[dbo].[XMLSOURCE]" Column "XMLDATA" using- procedure

Sample of XMLData:

  <InternetBrowser>    <Browser>      <Ranking>1</Ranking>      <Product>Firefox</Product>      <Rating>9.85</Rating>    </Browser>    ............................    </Browser>    <Browser>      <Ranking>7</Ranking>      <Product>Maxthon</Product>    </Browser>  </InternetBrowser>    
  1. I am storing XML Field data into "[Browser_Info]" Table using Cross-Join as below:

    INSERT INTO Browser_Info(ID,Ranking,Product,Rating) SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS NUMBER,DT.Ranking, DT.Product, ISNULL(DT.Rating,0) From (SELECT p.value('(./Ranking)[1]', 'int') AS Ranking, p.value('(./Product)[1]', 'VARCHAR(150)') AS Product, p.value('(./Rating)[1]', 'decimal(18,2)') AS Rating FROM XMLSOURCE CROSS APPLY XMLDATA.nodes('/InternetBrowser/Browser') t(p) ) AS DT

I am using ISNULL("field_Name", default_value) to remove error. How can I escape those field if data or XML Tag does not exist during XML-parsing and store NULL into Table Column.

Any better alternate to this issue. would be greatly appreciates.

No comments:

Post a Comment