Get pick list from XML data file
-
I don't have much experience with XML and the question may sound elementary... but I have spent already a couple of hours on it and I do not see / cannot find a good solution...
Take a look at the sample XML below - and note the node <CustomFields> - which contains the node <CustomField>
The XML may (or may not) have more than one node <CustomField>
I can retrieve the name (and value) of all <CustomField> node - no problem there.
On the other hand, I have not managed yet to retrieve the list of <item> in the node <pickList>... for a specific <CustomField>....
To explain: I need to obtain a data table that will later be used by an application to populate drop down lists (pick lists). Looking at the sample XML (see below) I would therefore need to obtain (select) a table / list containing the following:
1 - Client retiree
2 - Former client employee
3 - Contingent Worker
No
I just hope this was clear enough... and thanks in advance for any help...
<?xml version="1.0" encoding="UTF-8"?><StaffingOrder>
<OrderId validFrom="12/31/2015" validTo="12/31/2015">
<IdValue>JNJNJP00040440</IdValue>
<Status>Submitted</Status>
</OrderId>
<StaffingPosition>
<CustomFields>
<CustomField name="Has Candidate previously worked at Client?" mandatory="yes" type="xsd:pickList">
<pickList isCompleteList="yes" totalNumberOfItems="4">
<item>1 - Client retiree</item>
<item>2 - Former client employee</item>
<item>3 - Contingent Worker</item>
<item>No</item>
</pickList>
<Value />
<Class>JobSeeker</Class>
</CustomField>
</CustomFields>
</StaffingPosition>
</StaffingOrder>
-
Here is a rather hacky solution using Jeff Moden's DelimitedSplit8K function:
DECLARE @x XML = '<?xml version="1.0" encoding="UTF-8"?><StaffingOrder>
<OrderId validFrom="12/31/2015" validTo="12/31/2015">
<IdValue>JNJNJP00040440</IdValue>
<Status>Submitted</Status>
</OrderId>
<StaffingPosition>
<CustomFields>
<CustomField name="Has Candidate previously worked at Client?" mandatory="yes" type="xsd:pickList">
<pickList isCompleteList="yes" totalNumberOfItems="4">
<item>1 - Client retiree</item>
<item>2 - Former client employee</item>
<item>3 - Contingent Worker</item>
<item>No</item>
</pickList>
<Value />
<Class>JobSeeker</Class>
</CustomField>
</CustomFields>
</StaffingPosition>
</StaffingOrder>'
SELECT item
FROM dbo.DelimitedSplit8K(replace(replace(cast(@x.query('/StaffingOrder/StaffingPosition/CustomFields/CustomField/pickList/item') AS VARCHAR(8000)), '<item>', ''), '</item>', '|'), '|')
WHERE item <> ''
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
another way...
DECLARE @x XML = '<?xml version="1.0" encoding="UTF-8"?>
<StaffingOrder>
<OrderId validFrom="12/31/2015" validTo="12/31/2015">
<IdValue>JNJNJP00040440</IdValue>
<Status>Submitted</Status>
</OrderId>
<StaffingPosition>
<CustomFields>
<CustomField name="Has Candidate previously worked at Client?" mandatory="yes" type="xsd:pickList">
<pickList isCompleteList="yes" totalNumberOfItems="4">
<item>1 - Client retiree</item>
<item>2 - Former client employee</item>
<item>3 - Contingent Worker</item>
<item>No</item>
</pickList>
<Value />
<Class>JobSeeker</Class>
</CustomField>
</CustomFields>
</StaffingPosition>
</StaffingOrder>'
SELECT m.value('(text())[1]', 'varchar(100)')
FROM (VALUES (@x)) xx(x)
CROSS APPLY x.nodes('//item') mm(m);
"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."-- Itzik Ben-Gan 2001
-
Nice one, Alan. Much tidier.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Phil Parkin (12/15/2015)
Nice one, Alan. Much tidier.Thanks Phil...
And since I had a few minutes to kill during lunch, a set-based solution that uses no XML...
-- declaring as varchar for brevity
DECLARE @string varchar(8000) =
'<?xml version="1.0" encoding="UTF-8"?>
<StaffingOrder>
<OrderId validFrom="12/31/2015" validTo="12/31/2015">
<IdValue>JNJNJP00040440</IdValue>
<Status>Submitted</Status>
</OrderId>
<StaffingPosition>
<CustomFields>
<CustomField name="Has Candidate previously worked at Client?" mandatory="yes" type="xsd:pickList">
<pickList isCompleteList="yes" totalNumberOfItems="4">
<item>1 - Client retiree</item>
<item>2 - Former client employee</item>
<item>3 - Contingent Worker</item>
<item>No</item>
</pickList>
<Value />
<Class>JobSeeker</Class>
</CustomField>
</CustomFields>
</StaffingPosition>
</StaffingOrder>';
-- This routine could be an inline table valued function....
DECLARE @StartTag varchar(100) = '<item>',
@EndTag varchar(100) = '</item>';
WITH
L1 AS (SELECT N=1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))x(x)),
L3 AS (SELECT N=1 FROM L1 a, L1 b, L1 c),
iTally AS (SELECT N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM L3 a, L3 b),
SS1 AS
(
SELECT TOP (LEN(@string))
RN = ROW_NUMBER() OVER (ORDER BY N),
StartPos = N+LEN(@StartTag),
StartTag = @StartTag
FROM iTally
WHERE SUBSTRING(@string,N,LEN(@StartTag)) = @StartTag
),
SS2 AS
(
SELECT TOP (LEN(@string))
RN = ROW_NUMBER() OVER (ORDER BY N),
N,
EndTag = @EndTag
FROM iTally
WHERE SUBSTRING(@string,N,LEN(@EndTag)) = @EndTag
)
SELECT SUBSTRING(@string,StartPos, N-StartPos)
FROM SS1, SS2
WHERE SS1.RN = SS2.RN;
"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."-- Itzik Ben-Gan 2001
-
Thanks everyone! All solutions seem to work just fine... and I am inclined to work with this one (I still have to fully understand what it actually does)...
SELECT m.value('(text())[1]', 'varchar(100)')
FROM (VALUES (@x)) xx(x)
CROSS APPLY x.nodes('//item') mm(m);
BUT... as I mentioned... the XML may contain more than one <CustomField> node... so how would I manage to select only the items in a specific <CustomField> node...which is uniquely identified by an attribute...
<CustomField name="Has Candidate previously worked at Client?" mandatory="yes" type="xsd:pickList">...
Something like...
SELECT m.value('(text())[1]', 'varchar(100)')
FROM (VALUES (@x)) xx(x)
CROSS APPLY x.nodes('//item') mm(m)
WHERE CustomField attribute name = 'Has Candidate previously worked at Client?'
Thanks so much again - I still have a LOT to learn about this whole XML thingy...
-
Alan.B (12/15/2015)
another way...DECLARE @x XML = '<?xml version="1.0" encoding="UTF-8"?>
<StaffingOrder>
<OrderId validFrom="12/31/2015" validTo="12/31/2015">
<IdValue>JNJNJP00040440</IdValue>
<Status>Submitted</Status>
</OrderId>
<StaffingPosition>
<CustomFields>
<CustomField name="Has Candidate previously worked at Client?" mandatory="yes" type="xsd:pickList">
<pickList isCompleteList="yes" totalNumberOfItems="4">
<item>1 - Client retiree</item>
<item>2 - Former client employee</item>
<item>3 - Contingent Worker</item>
<item>No</item>
</pickList>
<Value />
<Class>JobSeeker</Class>
</CustomField>
</CustomFields>
</StaffingPosition>
</StaffingOrder>'
SELECT m.value('(text())[1]', 'varchar(100)')
FROM (VALUES (@x)) xx(x)
CROSS APPLY x.nodes('//item') mm(m);
Careful with the traversing here Alan, it can be quite expensive.
😎
-
Simplest way of writing this query would be a single nodes method on an XML variable
😎
DECLARE @TTXML XML = '<?xml version="1.0" encoding="UTF-8"?><StaffingOrder>
<OrderId validFrom="12/31/2015" validTo="12/31/2015">
<IdValue>JNJNJP00040440</IdValue>
<Status>Submitted</Status>
</OrderId>
<StaffingPosition>
<CustomFields>
<CustomField name="Has Candidate previously worked at Client?" mandatory="yes" type="xsd:pickList">
<pickList isCompleteList="yes" totalNumberOfItems="4">
<item>1 - Client retiree</item>
<item>2 - Former client employee</item>
<item>3 - Contingent Worker</item>
<item>No</item>
</pickList>
<Value />
<Class>JobSeeker</Class>
</CustomField>
</CustomFields>
</StaffingPosition>
</StaffingOrder>';
SELECT
STAFFORDER.DATA.value('./text()[1]','VARCHAR(100)')
FROM @TTXML.nodes('StaffingOrder/StaffingPosition/CustomFields/CustomField/pickList/item') AS STAFFORDER(DATA)
-
Thanks all, once again. These are great solutions that I am studying to understand more... BUT... my problem is ALSO that the XML contains more than one <CustomField>... and I need to retrieve the items in PickList only for a certain <CustomField> according to its "name" attribute....
As I mentioned in a previous message... something like...
SELECT m.value('(text())[1]', 'varchar(100)')
FROM (VALUES (@x)) xx(x)
CROSS APPLY x.nodes('//item') mm(m)
WHERE CustomField attribute name = 'Has Candidate previously worked at Client?'
I have tried several things... with no results (and all sort of very entertaining error messages)...
Hope I am not being too demanding, but it would be great if anyone could come up with a hint...!!!
Thanks all again...
-
Would you be so good as to provide an expanded example of the XML source data which includes multiple custom field definitions? Makes it easier for people to test their ideas.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Phil Parkin (12/16/2015)
Would you be so good as to provide an expanded example of the XML source data which includes multiple custom field definitions? Makes it easier for people to test their ideas.Piling on Phil's post, can you produce a "complex" example, pretty certain that these guys can produce a good solution.
😎
-
I am attaching a file that contains (more or less) the entire submission that I am receiving (had to change file extention to TXT - because XML is not a permitted attachment type)...
This sample demonstrates how the file can contain multiple <CustomField> nodes, each with its own <PickList>...
The screenshot below shows the file opened in good old XML Notepad... to better show the file structure...
So, for example and looking at the picture... how do I get all the pick list items for the <CustomField> node having a name of "Reason for Request"...
Thanks again !
-
Using your attached example you would modify the XPath Expression like so
*:StaffingOrder/*:StaffingPosition/*:CustomFields/*:CustomField[@name="Reason for Request"]/*:pickList/*:item'Here's the full solution:
DECLARE @x XML =
'<?xml version="1.0" encoding="UTF-8"?>
<StaffingOrder xmlns="jobPosting" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="jobPosting https://xuat.fgvms.com/schema/InSiteJobPostingV2.xsd">
<OrderId validFrom="12/31/2015" validTo="12/31/2015">
<IdValue>JNJNJP00040440</IdValue>
<Status>Submitted</Status>
</OrderId>
<ReferenceInformation>
<StaffingSupplierId>
<IdValue>EXSG</IdValue>
</StaffingSupplierId>
<StaffingCustomerId>
<IdValue>JNJN</IdValue>
</StaffingCustomerId>
</ReferenceInformation>
<BusinessUnit>1220</BusinessUnit>
<ClosedReasonName />
<OrderClassification orderType="Training and Development Manager" orderStatus="new" />
<Comments>
<Comment />
<CreatorName />
<CreateTime />
</Comments>
<OrderContact contactType="placedBy">
<ContactInfo>
<PersonName>
<LegalName>JNJN@fg_help</LegalName>
<FormattedName>Administrator, Fieldglass</FormattedName>
</PersonName>
</ContactInfo>
</OrderContact>
<RequiredResponseDate>12/31/2015</RequiredResponseDate>
<PositionQuantity>5</PositionQuantity>
<MaxSubmissionsAllowed>10</MaxSubmissionsAllowed>
<CanSubmitJobSeeker>yes</CanSubmitJobSeeker>
<StaffingPosition>
<PositionHeader>
<PositionTitle>Training and Development Manager</PositionTitle>
<FormattedPositionDescription>
<Value>Job Title: Senior Trainer</Value>
</FormattedPositionDescription>
</PositionHeader>
<PrimaryDepartment>PACKAGING.</PrimaryDepartment>
<Departments>
<Department>
<Code>62052 - 120R</Code>
<Name>PACKAGING.</Name>
<Allocation>100.000</Allocation>
</Department>
</Departments>
<PositionDateRange>
<StartDate>12/31/2015</StartDate>
<ExpectedEndDate>12/31/2015</ExpectedEndDate>
<CreateDate>12/14/2015</CreateDate>
<SubmitDate>12/14/2015</SubmitDate>
<ClosedDate />
</PositionDateRange>
<ServiceType>Temp</ServiceType>
<JobType>Full Time</JobType>
<ReportToPerson>
<ContactInfo>
<PersonName>
<LegalName>11005</LegalName>
<FormattedName>Wallach, Adam</FormattedName>
</PersonName>
</ContactInfo>
</ReportToPerson>
<Coordinator>
<ContactInfo>
<PersonName>
<LegalName>harveyt</LegalName>
<FormattedName>Harvey, Tracey</FormattedName>
</PersonName>
</ContactInfo>
</Coordinator>
<Distributor>
<ContactInfo>
<PersonName>
<LegalName>harveyt</LegalName>
<FormattedName>Harvey, Tracey</FormattedName>
</PersonName>
</ContactInfo>
</Distributor>
<WorkSite>
<WorkSiteId>
<Id>Janssen / 1001 US HIGHWAY ROUTE 202 / RARITAN / NJ / 08869</Id>
</WorkSiteId>
<Address>1001 US HIGHWAY 202</Address>
<Address2 />
<City>RARITAN</City>
<StateCode>NJ</StateCode>
<PostalCode>8869</PostalCode>
</WorkSite>
<Rates rateType="bill" rateStatus="agreed">
<BillAmount rateAmountPeriod="Hr" currency="USD">67.00</BillAmount>
<PayAmount rateAmountPeriod="Hr" currency="USD">0.00</PayAmount>
<Class>ST</Class>
<Markup />
<Factor />
<BaseClass />
</Rates>
<Rates rateType="bill" rateStatus="agreed">
<BillAmount rateAmountPeriod="Hr" currency="USD">93.80</BillAmount>
<PayAmount rateAmountPeriod="Hr" currency="USD">0.00</PayAmount>
<Class>OT</Class>
<Markup />
<Factor>1.400</Factor>
<BaseClass>ST</BaseClass>
</Rates>
<Rates rateType="bill" rateStatus="agreed">
<BillAmount rateAmountPeriod="Hr" currency="USD">120.60</BillAmount>
<PayAmount rateAmountPeriod="Hr" currency="USD">0.00</PayAmount>
<Class>DT</Class>
<Markup />
<Factor>1.800</Factor>
<BaseClass>ST</BaseClass>
</Rates>
<CustomFields>
<CustomField name="Critical Position?" mandatory="yes" type="xsd:pickList">
<pickList isCompleteList="yes" totalNumberOfItems="2">
<item>No</item>
<item>Yes</item>
</pickList>
<Value>No</Value>
<Class>JobPosting</Class>
</CustomField>
<CustomField name="Reason for Request" mandatory="yes" type="xsd:pickList">
<pickList isCompleteList="yes" totalNumberOfItems="11">
<item>*To be entered by the Concierge Center</item>
<item>Backfill for FTE</item>
<item>Contractor Replacement</item>
<item>Job Posting Re-Created for Existing Worker</item>
<item>Leave of Absence</item>
<item>Open Position</item>
<item>Other</item>
<item>Special Project</item>
<item>Special Skill Required</item>
<item>Worker Secondary Assignment</item>
<item>Workload</item>
</pickList>
<Value>*To be entered by the Concierge Center</Value>
<Class>JobPosting</Class>
</CustomField>
<CustomField name="Has Candidate previously worked at Client?" mandatory="yes" type="xsd:pickList">
<pickList isCompleteList="yes" totalNumberOfItems="4">
<item>1 - Client retiree</item>
<item>2 - Former client employee</item>
<item>3 - Contingent Worker</item>
<item>No</item>
</pickList>
<Value />
<Class>JobSeeker</Class>
</CustomField>
<CustomField name="If selected, will the Candidate be paid directly by your company?" mandatory="yes" type="xsd:pickList">
<pickList isCompleteList="yes" totalNumberOfItems="2">
<item>No</item>
<item>Yes</item>
</pickList>
<Value />
<Class>JobSeeker</Class>
</CustomField>
<CustomField name="If hired, will job seeker work on a W2 directly with your company?" mandatory="yes" type="xsd:pickList">
<pickList isCompleteList="yes" totalNumberOfItems="2">
<item>No</item>
<item>Yes</item>
</pickList>
<Value />
<Class>JobSeeker</Class>
</CustomField>
<CustomField name="Licensed Physician" mandatory="yes" type="xsd:pickList">
<pickList isCompleteList="yes" totalNumberOfItems="2">
<item>No</item>
<item>Yes</item>
</pickList>
<Value />
<Class>JobSeeker</Class>
</CustomField>
<CustomField name="Previous Client Experience Details" mandatory="yes" type="xsd:string">
<Value />
<Class>JobSeeker</Class>
</CustomField>
<CustomField name="Contractor Type" mandatory="yes" type="xsd:pickList">
<pickList isCompleteList="yes" totalNumberOfItems="4">
<item>Contingent (W2)</item>
<item>Payrollee (Non-Qualified IC, W2)</item>
<item>Payrollee (W2)</item>
<item>Pre-Identified (W2)</item>
</pickList>
<Value />
<Class>JobSeeker</Class>
</CustomField>
</CustomFields>
<Quals />
<PositionRules>
<AutoEngage>no</AutoEngage>
<AutoRegister>no</AutoRegister>
<AutoActivate>no</AutoActivate>
<PayRateRequired>yes</PayRateRequired>
<ResumeRequired>yes</ResumeRequired>
<JobSeekerBillRateCanExceedRequestedBillRate>yes</JobSeekerBillRateCanExceedRequestedBillRate>
<SupplierMustEnterRateChangeAmountsAndDurationOnJobSeekers>no</SupplierMustEnterRateChangeAmountsAndDurationOnJobSeekers>
<AllowSupplierToSelectJobSeekerSite>no</AllowSupplierToSelectJobSeekerSite>
</PositionRules>
<InvoiceInfo>
<AutoInvoiceType>Both</AutoInvoiceType>
<TrialEndDate />
<TravelTime>0.000</TravelTime>
<HoursPerDay>8.00</HoursPerDay>
<HoursPerWeek>40.00</HoursPerWeek>
<BillablePerDiem>0.00</BillablePerDiem>
<EstimatedAdditionalSpend>14070.00</EstimatedAdditionalSpend>
<EstimatedTotalHours>200.0</EstimatedTotalHours>
<EstimatedExpenses>0.000</EstimatedExpenses>
<SiteTax>0.000</SiteTax>
<MaximumExpenses>0.00</MaximumExpenses>
<ExternalReqNumber />
<PurchaseOrderNumber />
</InvoiceInfo>
</StaffingPosition>
</StaffingOrder>
';
SELECT m.value('(text())[1]', 'varchar(100)')
FROM (VALUES (@x)) xx(x)
CROSS APPLY x.nodes('*:StaffingOrder/*:StaffingPosition/*:CustomFields/*:CustomField[@name="Reason for Request"]/*:pickList/*:item') mm(m);
"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."-- Itzik Ben-Gan 2001
-
Eirikur Eiriksson (12/16/2015)
Alan.B (12/15/2015)
another way...DECLARE @x XML = '<?xml version="1.0" encoding="UTF-8"?>
<StaffingOrder>
<OrderId validFrom="12/31/2015" validTo="12/31/2015">
<IdValue>JNJNJP00040440</IdValue>
<Status>Submitted</Status>
</OrderId>
<StaffingPosition>
<CustomFields>
<CustomField name="Has Candidate previously worked at Client?" mandatory="yes" type="xsd:pickList">
<pickList isCompleteList="yes" totalNumberOfItems="4">
<item>1 - Client retiree</item>
<item>2 - Former client employee</item>
<item>3 - Contingent Worker</item>
<item>No</item>
</pickList>
<Value />
<Class>JobSeeker</Class>
</CustomField>
</CustomFields>
</StaffingPosition>
</StaffingOrder>'
SELECT m.value('(text())[1]', 'varchar(100)')
FROM (VALUES (@x)) xx(x)
CROSS APPLY x.nodes('//item') mm(m);
Careful with the traversing here Alan, it can be quite expensive.
😎
I was being lazy.
I'm not disagreeing with you but have you ever seen that kill performance in SQL Server? I'm curious - I have never seen a performance penalty traversing like that. I have seen a performance penalty for traversing like that while writing XSLT transforms but never using SQLXML in SQL Server. Just curious.
"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."-- Itzik Ben-Gan 2001
-
Alan.B (12/16/2015)
I was being lazy.I'm not disagreeing with you but have you ever seen that kill performance in SQL Server? I'm curious - I have never seen a performance penalty traversing like that. I have seen a performance penalty for traversing like that while writing XSLT transforms but never using SQLXML in SQL Server. Just curious.
This is one of the things that isn't easy to measure unless one has proper volumes, I've seen the additional calls to the XML Reader XPath filter function implied by the traversing hogging a system with less than 5K transactions a minute, once I got rid of it we were doing 5K transactions a second with less load on the system than before;-)
😎
Have a look at the execution plans, you will be surprised by the difference.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply