July 20, 2009 at 4:19 pm
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:
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:
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
July 20, 2009 at 6:47 pm
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 '&', '' 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.
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')
July 21, 2009 at 8:38 am
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 ."
(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
November 5, 2013 at 4:52 pm
Would really appreciate it. I would like to build a sql query for this schema.
Thanks
S
November 5, 2013 at 6:51 pm
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 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply