SQLServerCentral / SQL Server 2005 / T-SQL (SS2K5) / FOR XML EXPLICIT - One to many relationship at level 3 (3 = parent, 4 = child) / Latest Posts

RE: FOR XML EXPLICIT - One to many relationship at level 3 (3 = parent, 4 = child)

LSAdvantage — Tue, 21 Jul 2009 08:38:22 GMT

Thank you VERY much! I had tried adding a fourth union and had definitely been doing it wrong. And I year you on the CDATA portion. Here is what the spec said:"For XML files we recommend the use of CDATA Tags in all fields, but minimally in the nText fields. Otherwise the escaped values of the reserved XML characters will be the actual text entered into the database. For example, & l t ; b r & g t ; will be entered in the database instead of just < b r >."(Had to throw a lot of spaces in there to get the text to display properly.)For whatever it's worth, this is from ADP. We are getting rid of a third party recruiting system and will be utilizing something ADP offers and therefore need to get that legacy data into the new system.Appreciate the help! This is the best technical forum!!Lisa

RE: FOR XML EXPLICIT - One to many relationship at level 3 (3 = parent, 4 = child)

andrewd.smith — Mon, 20 Jul 2009 18:47:47 GMT

Try this FOR XML EXPLICIT query.[code]SELECT 1 AS Tag, 0 AS Parent, NULL AS [REQUISITIONS!1], NULL AS [RECORD!2!EMPLOYEEID!element], NULL AS [RECORD!2!FIRSTNAME!element], NULL AS [RECORD!2!LASTNAME!element], NULL AS [RECORD!2!JOBTITLE!cdata], NULL AS [LANGUAGES!3], NULL AS [LANGUAGE!4]UNION ALLSELECT 2 AS Tag, 1 AS Parent, NULL, EmployeeID, FirstName, LastName, JobTitle, NULL, NULLFROM @EmployeeUNION ALLSELECT 3 AS Tag, 2 AS Parent, NULL, EmployeeID, NULL, NULL, NULL, NULL, NULLFROM @EmployeeUNION ALLSELECT 4 AS Tag, 3 as Parent, NULL, EL.EmployeeID, NULL, NULL, NULL, NULL, L.LanguageNameFROM @EmployeeLanguage EL INNER JOIN @Language L ON (EL.LanguageID = L.LanguageID)ORDER BY [RECORD!2!EMPLOYEEID!element], [LANGUAGE!4]FOR XML EXPLICIT[/code]However, I would question why you really must have CDATA sections. The FOR XML PATH statement will properly encode XML character entities (e.g. '&' as '&', '<' as '<' and '>' as '>') and any decent XML parser should see no difference between encoded XML character entities and the equivalent unencoded characters in a CDATA section. Here is an alternative query that uses FOR XML PATH.[code]SELECT E.EmployeeID AS EMPLOYEEID, E.FirstName AS FIRSTNAME, E.LastName AS LASTNAME, E.JobTitle AS JOBTITLE, ( SELECT L.LanguageName AS '*' FROM @Language L INNER JOIN @EmployeeLanguage EL ON (L.LanguageID = EL.LanguageID) WHERE (EL.EmployeeID = E.EmployeeID) FOR XML PATH('LANGUAGE'), ROOT('LANGUAGES'), Type ) FROM @Employee EFOR XML PATH('RECORD'), ROOT('REQUISITIONS')[/code]

FOR XML EXPLICIT - One to many relationship at level 3 (3 = parent, 4 = child)

LSAdvantage — Mon, 20 Jul 2009 16:19:01 GMT

I am trying to generate a one time export to send to a vendor. They are requesting XML and for one of the items, the spec indicates "One parent tag – multiple child tags". I'm really close, but am struggling with this particular parent/child level. I need to use FOR XML EXPLICIT because they want CDATA tags on certain fields.I received a small sample file and am on the right track, but the sample file did not include an example of this particular parent/child section so I [i]believe[/i] this is the expected end result (based on the "One parent tag - multiple child tags" reference). NOTE: My problem area is "Languages".Expected Result:[code="xml"] 101 Jane Doe English 102 John Smith English Spanish 103 Erin Thopmson French Greek Italian [/code]I've created some sample tables to help illustrate my problem with my SQL statement at the end:[code]-- Create table variablesdeclare @Employee table (EmployeeID int NOT NULL, FirstName varchar(30) NOT NULL, LastName varchar(30) NOT NULL, JobTitle varchar(50) NOT NULL)--declare @Language table (LanguageID int NOT NULL, LanguageName varchar(30) NOT NULL)--declare @EmployeeLanguage table (EmployeeID int NOT NULL, LanguageID int NOT NULL)---- Populate table variables with sample datainsert into @Language values (1, 'English')insert into @Language values (2, 'Spanish')insert into @Language values (3, 'French')insert into @Language values (4, 'Italian')insert into @Language values (5, 'Greek')--insert into @Employee values (101, 'Jane', 'Doe', 'Accountant - Level I')insert into @Employee values (102, 'John', 'Smith', 'Business Analyst')insert into @Employee values (103, 'Erin', 'Thopmson', 'Database Administrator')--insert into @EmployeeLanguage values (101, 1)insert into @EmployeeLanguage values (102, 1)insert into @EmployeeLanguage values (102, 2)insert into @EmployeeLanguage values (103, 3)insert into @EmployeeLanguage values (103, 4)insert into @EmployeeLanguage values (103, 5)---- Render as XMLselect 1 as Tag, NULL as Parent, NULL as 'Requisitions!1', NULL as 'Record!2!EmployeeID!element', NULL as 'Record!2!FirstName!element', NULL as 'Record!2!LastName!element', NULL as 'Record!2!JobTitle!cdata', NULL as 'Languages!3!language!element'unionSELECT 2 as Tag, 1 as Parent, NULL, EmployeeID, FirstName, LastName, JobTitle, NULLFROM @Employeeunion allselect 3 as Tag, 2 as Parent, NULL, e.EmployeeID, e.FirstName, e.LastName, e.JobTitle, l.LanguageNamefrom @Employee einner join @EmployeeLanguage el on e.EmployeeID = el.EmployeeIDinner join @Language l on el.LanguageID = l.LanguageIDorder by 'Record!2!EmployeeID!element', 'Languages!3!language!element'for xml explicit[/code]But this is rendering individual Language nodes for each child language. For example, for Erin Thompson:[code="xml"] 103 Erin Thopmson French Greek Italian [/code]I've looked at several articles trying to figure out how to do this and I just can't get Languages to render correctly. It's almost making me wonder: Is what I'm trying to do not possible?? Or am I missing something?Thank you for any guidance -Lisa