CODEWITHSUNDEEP
sql-serverxmlt-sql
darky126
darky126

Reputation: 23

xml to sql server issue

I have one xml code which i would use for insert data to sql server table. I want to parse this xml and insert to sql server table. 

<?xml version="1.0" encoding="utf-8"?>

<resources>
    <color name="red_50">#FFEBEE</color>
    <color name="red_100">#FFCDD2</color>
    <color name="red_200">#EF9A9A</color>
    <color name="red_300">#E57373</color>
    <color name="red_400">#EF5350</color>
    <color name="red_500">#F44336</color>
    <color name="red_600">#E53935</color>
    <color name="red_700">#D32F2F</color>
    <color name="red_800">#C62828</color>
    <color name="red_900">#B71C1C</color>
    <color name="red_A100">#FF8A80</color>
    <color name="red_A200">#FF5252</color>
    <color name="red_A400">#FF1744</color>
    <color name="red_A700">#D50000</color>
</resources>

Example: For 

<color name="orange_50">#FFF3E0</color>

The main problem is to get : name:orange_50, hue:orange, value:50, hexcolor:#FFF3E0 and save all data to table.

This is what I have tried : 

DECLARE @XML XML = '<resources> 
  <color name="red_600">#E53935</color>  
  <color name="red_700">#D32F2F</color>  
  <color name="red_800">#C62828</color>  
  <color name="red_900">#B71C1C</color>  
  <color name="red_A100">#FF8A80</color>  
  <color name="red_A200">#FF5252</color>  
  <color name="red_A400">#FF1744</color>  
  <color name="red_A700">#D50000</color> 
</resources>
'

SELECT NAME = Events.value('@name', 'varchar(25)')
FROM @XML.nodes('ressources/color') AS XTbl(Events)

i try to get color name and work fine but for get hexadecimal i don't know how do. And i want to extract in red_800 for example, red only for hue column and 800 for value column. Thanks

Upvotes: 0

Views: 51

Answers (1)

har07
har07

Reputation: 89285

This is one possible way :

SELECT 
  NAME = Events.value('@name', 'varchar(25)')
  ,HUE = SUBSTRING(Events.value('@name', 'varchar(25)'), 1, CHARINDEX('_', Events.value('@name', 'varchar(25)'))-1)
  ,VALUE = SUBSTRING(Events.value('@name', 'varchar(25)'), CHARINDEX('_', Events.value('@name', 'varchar(25)')) + 1, LEN(Events.value('@name', 'varchar(25)')))
  ,HEXCOLOR = Events.value('.', 'varchar(25)')
FROM @XML.nodes('/resources/color') AS XTbl(Events)

Sqlfiddle Demo

output :

|     NAME | HUE | VALUE | HEXCOLOR |
|----------|-----|-------|----------|
|  red_600 | red |   600 |  #E53935 |
|  red_700 | red |   700 |  #D32F2F |
|  red_800 | red |   800 |  #C62828 |
|  red_900 | red |   900 |  #B71C1C |
| red_A100 | red |  A100 |  #FF8A80 |
| red_A200 | red |  A200 |  #FF5252 |
| red_A400 | red |  A400 |  #FF1744 |
| red_A700 | red |  A700 |  #D50000 |

Related discussion : T-SQL get substring after specific character?*

*) The same trick used here to get part of the name attribute value that is located after _ for VALUE, and before _ for HUE columns

Upvotes: 1

Related Questions