I am new to XML-Data parse in SQL. Currently I am using Microsoft SQL Server Management Studio-2012.
- 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> -
I am storing XML Field data into "[Browser_Info]" Table using
Cross-Joinas 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