Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

FOR XML EXPLICIT - One to many relationship at level 3 (3 = parent, 4 = child) Expand / Collapse
Author
Message
Posted Monday, July 20, 2009 4:19 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 11:01 AM
Points: 170, Visits: 896
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 believe this is the expected end result (based on the "One parent tag - multiple child tags" reference). NOTE: My problem area is "Languages".

Expected Result:
<Requisitions>
<Record>
<EmployeeID>101</EmployeeID>
<FirstName>Jane</FirstName>
<LastName>Doe</LastName>
<JobTitle><![CDATA[Accountant - Level I]]></JobTitle>
<Languages>
<language>English</language>
</Languages>
</Record>
<Record>
<EmployeeID>102</EmployeeID>
<FirstName>John</FirstName>
<LastName>Smith</LastName>
<JobTitle><![CDATA[Business Analyst]]></JobTitle>
<Languages>
<language>English</language>
<language>Spanish</language>
</Languages>
</Record>
<Record>
<EmployeeID>103</EmployeeID>
<FirstName>Erin</FirstName>
<LastName>Thopmson</LastName>
<JobTitle><![CDATA[Database Administrator]]></JobTitle>
<Languages>
<language>French</language>
<language>Greek</language>
<language>Italian</language>
</Languages>
</Record>
</Requisitions>

I've created some sample tables to help illustrate my problem with my SQL statement at the end:
-- Create table variables
declare @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 data
insert 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 XML
select 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'
union
SELECT 2 as Tag,
1 as Parent,
NULL,
EmployeeID,
FirstName,
LastName,
JobTitle,
NULL
FROM @Employee
union all
select 3 as Tag,
2 as Parent,
NULL,
e.EmployeeID,
e.FirstName,
e.LastName,
e.JobTitle,
l.LanguageName
from @Employee e
inner join @EmployeeLanguage el on e.EmployeeID = el.EmployeeID
inner join @Language l on el.LanguageID = l.LanguageID
order by 'Record!2!EmployeeID!element', 'Languages!3!language!element'
for xml explicit

But this is rendering individual Language nodes for each child language. For example, for Erin Thompson:

  <Record>
<EmployeeID>103</EmployeeID>
<FirstName>Erin</FirstName>
<LastName>Thopmson</LastName>
<JobTitle><![CDATA[Database Administrator]]></JobTitle>
<Languages>
<language>French</language>
</Languages>
<Languages>
<language>Greek</language>
</Languages>
<Languages>
<language>Italian</language>
</Languages>
</Record>

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
Post #756230
Posted Monday, July 20, 2009 6:47 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, October 24, 2012 2:12 PM
Points: 1,212, Visits: 3,232
Try this FOR XML EXPLICIT query.

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 ALL
SELECT 2 AS Tag,
1 AS Parent,
NULL,
EmployeeID,
FirstName,
LastName,
JobTitle,
NULL,
NULL
FROM @Employee
UNION ALL
SELECT 3 AS Tag,
2 AS Parent,
NULL,
EmployeeID,
NULL,
NULL,
NULL,
NULL,
NULL
FROM @Employee
UNION ALL
SELECT 4 AS Tag,
3 as Parent,
NULL,
EL.EmployeeID,
NULL,
NULL,
NULL,
NULL,
L.LanguageName
FROM @EmployeeLanguage EL
INNER JOIN @Language L ON (EL.LanguageID = L.LanguageID)
ORDER BY [RECORD!2!EMPLOYEEID!element], [LANGUAGE!4]
FOR XML EXPLICIT

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 '&amp;', '<' as '&lt;' and '>' as '&gt;') 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.

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 E
FOR XML PATH('RECORD'), ROOT('REQUISITIONS')

Post #756283
Posted Tuesday, July 21, 2009 8:38 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 11:01 AM
Points: 170, Visits: 896
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
Post #756648
Posted Tuesday, November 5, 2013 4:52 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, May 27, 2014 1:22 PM
Points: 13, Visits: 172
Would really appreciate it. I would like to build a sql query for this schema.

Thanks
S
Post #1511669
Posted Tuesday, November 5, 2013 6:51 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 3:30 AM
Points: 3,420, Visits: 5,348
sindhuv.tx (11/5/2013)
Hello,

I have this schema given to me

<soapenv:Body>
<xxm:xxmesmo>
<xxm:systemName>TESTCP</xxm:systemName>
<xxm:companyId>1</xxm:companyId>
<xxm:document>

<![CDATA[
<!--document for xxmesmo,xsi namespace is added to support nulls in column values-->
<xxmesmo xmlns='http://www.deltek.com/enterprise/integration/xxmesmo' xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance'>
<!--Result set `Relieve Manufacturing Order` Supported Operations(INSERT SELECT )-->
<PCMRELMO_INVTTRN_HDR1 tranType='INSERT'>
<!--Column `Notes` String(254)-->
<COMMENT_NT>Test of the Webservice MO Relief</COMMENT_NT>
<!--Column `ETC Percent` Decimal(5,4)-->
<ETC_PCT_RT>1.0</ETC_PCT_RT>
<!--Column `Material Handler` Required String(12)-->
<MATL_HNDLR_EMPL_ID>10017296</MATL_HNDLR_EMPL_ID>
<!--Column `Manufacturing Order` MethodPK Required String(10)-->
<ORD_ID>FMO-000003</ORD_ID>
<!--Column `Partial Relief Costing Option` String(1) cb_S_ORD_REF_TYPE_CD-->
<S_ORD_REF_TYPE_CD>E</S_ORD_REF_TYPE_CD>
<!--Column `Transaction Date` Required DateTime-->
<TRN_DT>2013-10-24T00:00:00.0</TRN_DT>
<!--Result set `` Supported Operations(INSERT )-->
<PCMRELMO_INVTTRANSLINES_CTW tranType='INSERT'>
<INVT_ABBRV_CD_TO>FM0000</INVT_ABBRV_CD_TO>
<!--Column `To Location` String(15)-->
<INVT_LOC_ID_TO>01E01</INVT_LOC_ID_TO>
<!--Column `Relief Quantity` Required Decimal(14,4)-->
<TRN_QTY>1.0</TRN_QTY>
<!--Column `To Warehouse` String(8)-->
<WHSE_ID_TO>FL-01</WHSE_ID_TO>
<PCMRELMO_MMMSRLT tranType='INSERT'>
<!--Column `Lot Number` String(20)-->
<LOT_ID>FMO-000003-2</LOT_ID>
<!--Column `Maintenance Purchased` Required String(1) checkBox-->
<MAINT_FL>N</MAINT_FL>
<!--Column `Notes` String(254)-->
<NOTES_NT>Test WebServices MO Relief FMO-000003-2</NOTES_NT>
<!--Column `Quantity~Disposition Quantity` Decimal(14,4)-->
<PCMRELMO_MMMSRLT_TRN_QTY>1.0</PCMRELMO_MMMSRLT_TRN_QTY>
<!--Column `Serial Number` String(20)-->
<SERIAL_ID></SERIAL_ID>
</PCMRELMO_MMMSRLT>



</PCMRELMO_INVTTRANSLINES_CTW>

</PCMRELMO_INVTTRN_HDR1>

</xxmesmo>
]]>

</xxm:document>
</xxm:xxmesmo>
</soapenv:Body>
</soapenv:Envelope>

Here there are 3 levels inthe schema. All the info i need come fom one single sql table. can I use the query that you have shown to populate this schema. Can you guide me on this? Can you give me an example of how to do it. Would really appreciate it. I would like to build a sql query for this schema.

Thanks
S


It is really not recommended to hijack a 4 year old thread. My suggestion is that you start a new one.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1511690
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse