Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 131
  • Last Modified:

xquery working only for some columns/elements

Techies--
This stored procedure appears to be writing data correctly for some of the columns, but not for all of the columns. The attached file will generate the table.

USE [SuccessFactors]
GO
/****** Object:  StoredProcedure [staging].[LoadPositions]    Script Date: 10/23/2014 7:37:23 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		plditallo
-- Create date: 10/22/2014
-- Description:	Populates all Position data from OData version of SuccessFactors
--               to staging.Position
-- =============================================
ALTER PROCEDURE [staging].[LoadPositions]
-- Debug; when working, uncomment 
-- ( @xmlpositions xml
-- )
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

-- Debug; when working comment or remove next 2 lines
declare @xmlpositions xml
set @xmlpositions = '<Positions><Position><effectiveStartDate>2014-06-11T00:00:00</effectiveStartDate><code>25TEST</code><positionControlled></positionControlled><transactionSequence>1</transactionSequence><mdfSystemObjectType>Position</mdfSystemObjectType><location>L001</location><effectiveStatus>A</effectiveStatus><effectiveEndDate>9999-12-31T00:00:00</effectiveEndDate><vacant>false</vacant><description></description><technicalParameters>true;2014-06-11;false;true</technicalParameters><payGrade>ZHE1</payGrade><externalName_en_US>Comp Analyst</externalName_en_US><createdDate>2014-06-19T16:36:13</createdDate><mdfSystemRecordStatus>N</mdfSystemRecordStatus><multipleIncumbentsAllowed>true</multipleIncumbentsAllowed><businessUnit></businessUnit><createdBy>tpadmin</createdBy><company>T123</company><lastModifiedBy>tpadmin</lastModifiedBy><jobCode></jobCode><comment></comment><mdfSystemVersionId></mdfSystemVersionId><department>T200</department><criticality></criticality><division></division><cust_locationgroup></cust_locationgroup><lastModifiedDateWithTZ>2014-06-19T20:36:13Z</lastModifiedDateWithTZ><standardHours></standardHours><jobTitle>Hayleys Test Job</jobTitle><legacyPositionId></legacyPositionId><costCenter>11111</costCenter><targetFTE>1</targetFTE><cust_Workingdaysperweek></cust_Workingdaysperweek><positionTitle></positionTitle><lastModifiedDate>2014-06-19T16:36:13</lastModifiedDate><cust_loctype>LT1</cust_loctype></Position><Position><effectiveStartDate>2014-09-01T00:00:00</effectiveStartDate><code>VPR10001</code><positionControlled></positionControlled><transactionSequence>1</transactionSequence><mdfSystemObjectType>Position</mdfSystemObjectType><location>OK02</location><effectiveStatus>A</effectiveStatus><effectiveEndDate>9999-12-31T00:00:00</effectiveEndDate><vacant>false</vacant><description>Vice Pres of Tire Care and Fac Main</description><technicalParameters></technicalParameters><payGrade>PayGrade3</payGrade><externalName_en_US>Vice Pres of Tire Care and Fac Main</externalName_en_US><createdDate>2014-10-06T13:34:12</createdDate><mdfSystemRecordStatus>N</mdfSystemRecordStatus><multipleIncumbentsAllowed>true</multipleIncumbentsAllowed><businessUnit>Executive</businessUnit><createdBy>ImportAdmin</createdBy><company>1002</company><lastModifiedBy>ImportAdmin</lastModifiedBy><jobCode>VPR1</jobCode><comment></comment><mdfSystemVersionId></mdfSystemVersionId><department>800E</department><criticality></criticality><division>Executive</division><cust_locationgroup>OK</cust_locationgroup><lastModifiedDateWithTZ>2014-10-06T17:34:12Z</lastModifiedDateWithTZ><standardHours></standardHours><jobTitle>Vice President</jobTitle><legacyPositionId></legacyPositionId><costCenter>1002910100</costCenter><targetFTE>100</targetFTE><cust_Workingdaysperweek>5</cust_Workingdaysperweek><positionTitle></positionTitle><lastModifiedDate>2014-10-06T13:34:12</lastModifiedDate><cust_loctype>OFFI</cust_loctype></Position></Positions>'

    INSERT INTO staging.Position 
	(   [EffecitveStartDate],
		[Code],
		[PositionControlled],
		[TransactionSequence],
		[MDFSystemObjectType],
		[Location],
		[EffectiveStatus],
		[EffectiveEndDate],
		[Vacant],
		[Description],
		[TechnicalParameters],
		[PayGrade],
		[ExternalName_en_US],
		[CreatedDate],
		[MDFSystemRecordStatus],
		[MultipleIncumbentsAllowed],
		[BusinessUnit],
		[CreatedBy],
		[Company],
		[LastModifiedBy],
		[JobCode],
		[Comment],
		[MDFSystemVersionId],
		[Department],
		[Criticality],
		[Division],
		[cust_locationgroup],
		[LastModifiedDateWithTZ],
		[StandardHours],
		[JobTitle],
		[LegacyPositionId],
		[CostCenter],
		[TargetFTE],
		[cust_Workingdaysperweek],
		[PositionTitle],
		[LastModifiedDate],
		[cust_loctype]
	)
	SELECT
	     T.c.value('EffecitveStartDate[1]', 'varchar(30)')
		,T.c.value('Code[1]','varchar(20)')
		,T.c.value('PositionControlled[1]', 'varchar(10)')
		,T.c.value('TransactionSequence[1]','varchar(10)')
		,T.c.value('MDFSystemObjectType[1]','varchar(40)')
		,T.c.value('Location[1]','varchar(20)')
		,T.c.value('EffectiveStatus[1]','varchar(10)')
		,T.c.value('EffectiveEndDate[1]','varchar(30)')
		,T.c.value('Vacant[1]','varchar(10)')
		,T.c.value('Description[1]','varchar(200)')
		,T.c.value('TechnicalParameters[1]','varchar(200)')
		,T.c.value('PayGrade[1]','varchar(20)')
		,T.c.value('ExternalName_en_US[1]', 'varchar(100)')
		,T.c.value('CreatedDate[1]','varchar(30)')
		,T.c.value('MDFSystemRecordStatus[1]','varchar(40)')
		,T.c.value('MultipleIncumbentsAllowed[1]','varchar(10)')
		,T.c.value('BusinessUnit[1]','varchar(10)')
		,T.c.value('CreatedBy[1]','varchar(20)')
		,T.c.value('Company[1]','varchar(20)')
		,T.c.value('LastModifiedBy[1]','varchar(20)')
		,T.c.value('JobCode[1]','varchar(20)')
		,T.c.value('Comment[1]','varchar(500)')
		,T.c.value('MDFSystemVersionId[1]','varchar(40)')
		,T.c.value('Department[1]','varchar(40)')
		,T.c.value('Criticality[1]','varchar(20)')
		,T.c.value('Division[1]','varchar(30)')
		,T.c.value('cust_locationgroup[1]', 'varchar(100)')
		,T.c.value('LastModifiedDateWithTZ[1]','varchar(40)')
		,T.c.value('StandardHours[1]', 'varchar(20)')
		,T.c.value('JobTitle[1]','varchar(100)')
		,T.c.value('LegacyPositionId[1]','varchar(100)')
		,T.c.value('CostCenter[1]','varchar(50)')
		,T.c.value('TargetFTE[1]','varchar(30)')
		,T.c.value('cust_Workingdaysperweek[1]','varchar(40)')
		,T.c.value('PositionTitle[1]','varchar(100)')
		,T.c.value('LastModifiedDate[1]','varchar(30)')
		,T.c.value('cust_loctype[1]','varchar(50)')
	 FROM @xmlpositions.nodes('/Positions/Position') as T(c)


END

Open in new window

generatePositionTBL.sql
0
Paula DiTallo
Asked:
Paula DiTallo
1 Solution
 
ste5anSenior DeveloperCommented:
Cause XML is case-sensitive. Thus any XPath or XQuery expression must use the same case as in the element and attribute names.
0
 
Paula DiTalloIntegration developerAuthor Commented:
ste5an,
Thanks so much! :)
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now