SQL XML shredding, last node in xquery

Question

Hi,

I am trying to shred the data into a temporary table using xquery and nodes, but i am having a truble with the last 2 nodes.

     Node.value('.', 'date') AS Date_Joined,
     Node.value('.', 'bit') AS Member_Status

If i use '.' i am getting the default values in all my columns. If i use variables then it doesn't work at all.

Could you please help me?

Here is my code: 

DECLARE @xml XML;
 
SET @xml = 
(
select
CareTeams.care_team_id,
Doctors.doctor_id, Doctors.doctor_name,
CareTeamDoctors.date_joined as DateJoined,
CareTeamDoctors.current_member as CurrentMember
from dbo.care_team_tbl [CareTeams]
inner join dbo.care_team_doctor_tbl CareTeamDoctors on [CareTeams].care_team_id = CareTeamDoctors.care_team_id
inner join dbo.doctor_tbl Doctors on Doctors.doctor_id = CareTeamDoctors.doctor_member_id
where CareTeams.care_team_id = 3

for xml auto, root('thedata')
)

SELECT Node.value('../../@care_team_id', 'int') AS Care_Team_ID,  
  Node.value('../@doctor_id', 'int') AS Doctor_ID,  
  Node.value('../@doctor_name', 'varchar(50)') AS Doctor_Name,
  Node.value('.', 'date') AS Date_Joined,
  Node.value('.', 'bit') AS Member_Status 
FROM @xml.nodes('/thedata/CareTeams/Doctors/CareTeamDoctors') TempXML (Node);

Here is the picture for more details.

Answer 1

Hi C-Sharp Rabbit,

I tried to replicate your scenario and resolve using OpenXML , hope it helps, for more information please refer http://msdn.microsoft.com/en-us/library/aa226522(v=sql.80).aspx

Try 

 

CREATE TABLE TESTXML(CARE_TEAM_ID INT,DOCTORMEMBER INT,DATEJOINED DATETIME,CURRENT_MEMBER BIT)
INSERT INTO TESTXML VALUES(1,10000,GETDATE(),1),
(1,10001,GETDATE(),1),
(2,10000,GETDATE(),0),
(2,10001,GETDATE(),0)

SELECT * FROM TESTXML FOR XML AUTO, ROOT('THEDATA')

DECLARE @IDOC INT
DECLARE @DOC VARCHAR(1000)
SET @DOC =(SELECT * FROM TESTXML FOR XML AUTO, ROOT('THEDATA'))
EXEC SP_XML_PREPAREDOCUMENT @IDOC OUTPUT, @DOC
SELECT *
FROM OPENXML (@IDOC, '/THEDATA/TESTXML',1)
      WITH (CARE_TEAM_ID  INT,
            DOCTORMEMBER INT,
            DATEJOINED DATETIME,
            CURRENT_MEMBER INT)
EXEC SP_XML_REMOVEDOCUMENT @IDOC

---

Thanks
Manish

Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Answer 2

Try this.  There is no need to use the older way of shredding XML ( OPENXML ), you can use the methods of the xml datatype instead, eg nodes, value, query, exist:

SELECT 
	ct.c.value('@care_team_id', 'INT') AS Care_Team_ID,
	d.c.value('@doctor_id', 'INT') AS Doctor_ID,
	d.c.value('@doctor_name', 'VARCHAR(50)') AS Doctor_Name,
	ctd.c.value('@DateJoined', 'DATETIME') AS Doctor_Name,
	ctd.c.value('@CurrentMember', 'BIT') AS CurrentMember
FROM @xml.nodes('/thedata/CareTeams') ct(c)
	CROSS APPLY ct.c.nodes('Doctors') d(c)
		CROSS APPLY d.c.nodes('CareTeamDoctors') ctd(c)