Eirikur Eiriksson (5/23/2015)
Bex (5/22/2015)
I have a one-off need to get some XML data into a table
<Persons>
<Person>
<STAFFID>12345678910</STAFFID>
<SURANME>Bloggs</SURNAME>
<FIRSTNAME>Joe</FIRSTNAME>
<Department>
<DEPTID>100</DEPTID>
<DEPTNAME>Marketing</DEPTNAME>
<Role>
<RoleID>123</RoleID>
<Tenure>F</Tenure>
</Role>
</Department>
</Person>
</Persons>
Using
SELECT StaffID = x.data.value('STAFFID[1]','nvarchar(15)'),
Surname= x.data.value('SURNAME[1]','nvarchar(20)'),
Firstname= x.data.value('FIRSTNAME[1]','nvarchar(20)'),
DeptID= x.data.value('DEPTID[1]','nvarchar(5)'),
DeptName= x.data.value('DEPTNAME[1]','nvarchar(25)'),
RoleID= x.data.value('ROLEID[1]','nvarchar(5)'),
Tenure= x.data.value('TENURE[1]','nvarchar(5)')
FROM @Persons p
CROSS APPLY p.XMLData.nodes('Persons/Person') x(data)
I can get as far as staffid, surname, firstname (from person), but I am at a loss as to how to then add in the department and role data to give me
StaffID SurnameFirstnameDeptIDDeptNameRoleIDTenure
12345678910BloggsJoe100Marketing123F
Any help will be much appreciated.
Thanks
Bex
Quick points, the XML is case sensitive and there is no tolerance for typos or other data errors, had to fix the misspelling in the example.
😎
USE tempdb;
GO
SET NOCOUNT ON;
DECLARE @Persona TABLE (XMLData XML NOT NULL);
INSERT INTO @Persona(XMLData)
VALUES('<Persons>
<Person>
<STAFFID>12345678910</STAFFID>
<SURNAME>Bloggs</SURNAME>
<FIRSTNAME>Joe</FIRSTNAME>
<Department>
<DEPTID>100</DEPTID>
<DEPTNAME>Marketing</DEPTNAME>
<Role>
<RoleID>123</RoleID>
<Tenure>F</Tenure>
</Role>
</Department>
</Person>
</Persons>');
SELECT
PERS.DATA.value('(STAFFID/text())[1]' ,'NVARCHAR(25)') AS STAFFID
,PERS.DATA.value('(SURNAME/text())[1]' ,'NVARCHAR(25)') AS SURNAME
,PERS.DATA.value('(FIRSTNAME/text())[1]' ,'NVARCHAR(25)') AS FIRSTNAME
,PERS.DATA.value('(Department/DEPTNAME/text())[1]' ,'NVARCHAR(25)') AS DEPTNAME
,PERS.DATA.value('(Department/Role/RoleID/text())[1]','NVARCHAR(25)') AS RoleID
,PERS.DATA.value('(Department/Role/Tenure/text())[1]','NVARCHAR(25)') AS Tenure
FROM @Persona P
CROSS APPLY P.XMLData.nodes('Persons/Person') AS PERS(DATA);
Results
STAFFID SURNAME FIRSTNAME DEPTNAME RoleID Tenure
------------- --------- ----------- ----------- -------- --------
12345678910 Bloggs Joe Marketing 123 F
http://www.sqlservercentral.com/Forums/Skins/Classic/Images/RichTextBoxTable/tbl_bottomleft.gif
Thank you Eirikur, for both the code and advice. Always happy to learn.
Much appreciated.
Bex