INSERT multi-table foreign keys from XML

Question

INSERT multi-table foreign keys from XML

Viewing 4 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 1

I'm not sure I'm clear on your objective. Are you simply trying to get your three columns into a single select into the same table, or have I horribly misunderstood what you're asking?

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Carlo Clausius Say Hey Kid Points: 693 More actions · Answer 2

I'm not sure I'm clear on your objective. Are you simply trying to get your three columns into a single select into the same table, or have I horribly misunderstood what you're asking?

Yes. Right now I'm declaring three variables, assigning the value to the variables one by one, then doing the insert into the table using the three variables...tedious and I'm getting finger fatigue.:crazy:

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 3

Try the following, then just join normally:

DECLARE @xmlInsertRecordString XML

DECLARE @iDoc INT

SET @xmlInsertRecordString = '<AllForcast>

<SESA>SESA99999</SESA>

<EmployeeName>Alex Bea</EmployeeName>

<HiringStatus>Full Time</HiringStatus>

<EmployeeGroup>HW</EmployeeGroup>

<EmployeeSub-Group>HW</EmployeeSub-Group>

<City>Burnaby</City>

<ProjectStatus>Active</ProjectStatus>

<ProjectName>AB-1</ProjectName>

<ProjectCode>411</ProjectCode>

<ProjectManager>John Doe</ProjectManager>

<Notes></Notes>

<Date>Jan 1 2016</Date>

<Effort>1</Effort>

<RecordBy>SESA123456</RecordBy>

<RecordID>4</RecordID>

</AllForcast>'

EXEC sp_xml_preparedocument @iDoc OUTPUT, @xmlInsertRecordString

SELECT

*

FROM

OPENXML( @iDoc, '//AllForcast')

WITH (EmpSubGroup VARCHAR( 50) 'EmployeeSub-Group',

SESA VARCHAR(50) 'SESA',

ProjectCode VARCHAR(50) 'ProjectCode'

) AS ox

EXEC sp_xml_removedocument @iDoc

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Carlo Clausius Say Hey Kid Points: 693 More actions · Answer 4

Carlo Clausius

Say Hey Kid

Points: 693

August 7, 2014 at 4:54 pm

#1736245

Nice!!

Thank you.