Q:

How to insert a XML file into a SQL table

How do I insert this into a SQL table?
<ITEM id="1" 
    name="Swimmer Head" 
    mesh_name="eq_head_swim" 
    totalpoint="0" 
    type="equip" 
    res_sex="m" 
    res_level="0" 
    slot="head" 
    weight="2" 
    bt_price="0" 
    hp="4" 
    ap="8" 
    maxwt="0" 
    sf="0" 
    fr="0" 
    cr="0" 
    pr="0" 
    lr="0" 
    color="#FFFFFFFF" 
    desc="Part of an everyday swimming outfit" />

Also, theres a lot of more lines in this XML file, so how can I do this with 1 .sql file?

A:

Here is one method which will give you an EAV structure (Entity Attribute Value).
You may notice I only have to identify ONE key element ... id
I truncated a few elements and added a second item for demonstrative purposes only
Declare @XML xml = '
<ITEM id="1" name="Swimmer Head" mesh_name="eq_head_swim" totalpoint="0" type="equip" res_sex="m" res_level="0" slot="head" weight="2" bt_price="0" color="#FFFFFFFF" desc="Part of an everyday swimming outfit" />
<ITEM id="2" name="Boxer Feet" mesh_name="eq_feet_boxer" totalpoint="0" type="equip" res_sex="m" res_level="0" slot="head" weight="2" bt_price="25.00" color="#FFFFFFFF" desc="Somthing for the boxer" />
'

Select ID     = r.value('@id','int')
      ,Item   = attr.value('local-name(.)','varchar(100)')
      ,Value  = attr.value('.','varchar(max)') 
 From  @XML.nodes('/ITEM') as A(r)
 Cross Apply A.r.nodes('./@*') AS B(attr)
 Where attr.value('local-name(.)','varchar(100)') not in ('id')

Returns (which can easily be Pivoted if necessary)

EDIT - To load XML from a FILE

Declare @XML xml
Select @XML = BulkColumn FROM  OPENROWSET(BULK 'C:\Working\SomeXMLFile.xml', SINGLE_BLOB) x;

Select ID     = r.value('@id','int')
      ,Item   = attr.value('local-name(.)','varchar(100)')
      ,Value  = attr.value('.','varchar(max)') 
 From  @XML.nodes('/ITEM') as A(r)
 Cross Apply A.r.nodes('./@*') AS B(attr)
 Where attr.value('local-name(.)','varchar(100)') not in ('id')

