IMPORT XML DATA TO SQL SERVER

[alleybye]

Hi I have a question, I want to transfer all nodes to a datarow

Here is my XML

<Employee>
  <person>
  <firstName>Jon</firstName>
  <lastName>Johnson</lastName>
  <skills>Singing</skills>
  <skills>Dancing</skills>
  </person>
 </Employee>

THE HEADER TABLE SHOULD LOOK LIKE THIS WITH RELATION

Profile_ID | Firstname | Lastname|
10         | Jon       | Johnson |

AND THE DETAIL TABLE SHOULD LOOK LIKE THIS

SkillsID | Profile_ID |  Skills  |
1        | 10         | Singing  |
2        | 10         | Dancing  |

I have to TABLES, HEADER and DETAILS. I want to save the FIRSTNAME and LASTNAME on the HEADER table and the SKILLS on the DETAIL Table

HERE IS MY SQL CODE, I AM NOT REALLY GOOD AT QUERIES SORRY FOR THAT.

CREATE PROCEDURE SP_INSERT_XML_DATA2
    @exml XML
    AS
    BEGIN
    INSERT INTO Header(LastName,FirstName)
    SELECT  x.value('(firstName/text())[1]', 'nvarchar(max)') as FirstName,
            x.value('(lastName/text())[1]', 'nvarchar(max)') as LastName
            FROM @exml.nodes('/Employee/person') AS abc(x)


    DECLARE @SPLIT as VARCHAR(MAX), @STRING as VARCHAR(MAX)
    SET @STRING =  (SELECT  x.query('skills').value('.','nvarchar(max)') as Skills FROM @exml.nodes('/Employee/person') AS abc(x))
    SET @SPLIT = ','
    SET @exml = CAST(('<skills>'+REPLACE(@STRING,@SPLIT,'</skills><skills>')+'</skills>') AS XML)

    INSERT INTO Details(Skills_ID,Skills) VALUES
            ((SELECT TOP 1 ID FROM Header ORDER BY ID DESC),
            (SELECT x.value('.[1]', 'nvarchar(50)')FROM @exml.nodes('//skills') AS abc(x)))

    END

    EXEC SP_INSERT_XML_DATA2 @exml = '<Employee>
                                        <person>
                                            <firstName>Jon</firstName>
                                            <lastName>Johnson</lastName>
                                            <skills>Singing</skills>
                                            <skills>Singin2</skills>
                                            <skills>Singin3</skills>
                                        </person>
                                        </Employee>'

[alleybye]

I found a work arround. Thanks anyways.

ALTER PROCEDURE SP_INSERT_XML_DATA2
@exml XML
AS
BEGIN
INSERT INTO Header(LastName,FirstName)
SELECT  x.value('(firstName/text())[1]', 'nvarchar(max)') as FirstName,
        x.value('(lastName/text())[1]', 'nvarchar(max)') as LastName
        FROM @exml.nodes('/Employee/person') AS abc(x)

DECLARE @Iter int
SET @Iter = (SELECT TOP 1 ID FROM Header ORDER BY ID DESC)


INSERT INTO Details(Skills)
        ((SELECT T.C.value('.', 'varchar(100)') as Skills
FROM @exml.nodes('(/Employee/person/skills)') as T(C) ))

UPDATE Details
    SET Skills_ID = (SELECT TOP 1 ID FROM Header ORDER BY ID DESC) WHERE Skills_ID IS NULL

END

EXEC SP_INSERT_XML_DATA2 @exml = '<Employee>
                                    <person>
                                        <firstName>Jon</firstName>
                                        <lastName>Johnson</lastName>
                                        <skills>Singing</skills>
                                        <skills>Singin2</skills>
                                        <skills>Singin3</skills>
                                    </person>
                                  </Employee>'