January 12, 2014 at 9:29 pm
This xml file have to be converted into table format which is shown below.The 3rd column mentioned in the table have to be extracted based on maleworker and femaleworker tabs.
<?xml version="1.0"?>
<ROOt>
<Document Type="workersinput" Version="1.0.0" />
<Manager Name = "Robby">
<workers>
<Maleworker Value = "Antony">
<Femaleworker Value = "Fileebarta"/>
</Maleworker>
</workers>
</Manager>
<Manager Name = "Walton">
<workers>
<Maleworker Value = "Jhony">
<Femaleworke Value = "jennifer"/>
</Maleworker>
</workers>
</Manager>
</Root>
Output table :
Managerworkerssex
RobbyAntonyM
RobbyFileebartaF
WaltonJhony M
WaltonjenniferF
Please let me know if anyone knows the answer for this.
Thanks in advance.
January 12, 2014 at 11:41 pm
Hi all,
I solved this one as shown below. If anyone knows the easy and simply method then this, then pls let me know.
GO
DECLARE @xmlParameter XML =
'<?xml version="1.0"?>
<ROOt>
<Document Type="workersinput" Version="1.0.0" />
<Manager Name = "Robby">
<workers>
<Maleworker Value = "Antony">
<Femaleworker Value = "Fileebarta"/>
</Maleworker>
</workers>
</Manager>
<Manager Name = "Walton">
<workers>
<Maleworker Value = "Jhony">
<Femaleworker Value = "jennifer"/>
</Maleworker>
</workers>
</Manager>
</ROOt> '
DECLARE @idoc INT
DECLARE @doc XML
SET @doc = @xmlParameter
--DROP Table #BrandModel
CREATE TABLE #EMPLOYEE_1
(
MANAGERNVARCHAR(MAX) NULL,
WORKERS_M NVARCHAR(MAX) NULL,
SEX_MNVARCHAR(MAX) NULL,
WORKERS_F NVARCHAR(MAX) NULL,
SEX_FNVARCHAR(MAX) NULL
)
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
DECLARE @MALE Varchar(100) = 'M'
DECLARE @FEMALE Varchar(100) = 'F'
INSERT INTO #EMPLOYEE_1(MANAGER,WORKERS_M,SEX_M,WORKERS_F,SEX_F)
SELECT T1.MANAGER,T1.WORKERS_M,@MALE,T1.WORKERS_F,@FEMALE
FROM OPENXML (@idoc, '/ROOt/Manager/workers/Maleworker/Femaleworker')
WITH (MANAGER VARCHAR(100) '../../../@Name',WORKERS_M VARCHAR(100) '../@Value',WORKERS_F VARCHAR(100) '@Value')T1
CREATE TABLE #EMPLOYEE
(
MANAGERSNVARCHAR(MAX) NULL,
WORKERS NVARCHAR(MAX) NULL,
SEXNVARCHAR(MAX) NULL,
)
INSERT INTO #EMPLOYEE
SELECT MANAGER,WORKERS_M,SEX_M
FROM #EMPLOYEE_1
UNION
SELECT MANAGER,WORKERS_F,SEX_F
FROM #EMPLOYEE_1
EXEC SP_XML_REMOVEDOCUMENT @idoc
select * FROM #EMPLOYEE
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply