May 22, 2015 at 7:19 am
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
May 23, 2015 at 1:35 am
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
May 23, 2015 at 3:18 am
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
May 23, 2015 at 11:45 am
Happy to help and you are most welcome
😎
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply