Grinderhand
New Member
In MS SQL Server 2008 or 2008R2, how can we update XML with a number of values coming from another SQL database when the target XML node may not exist - and the parent of the target node may not exist either.Example:\[code\]<JobCandidate> <Name> <FirstName>Mike</FirstName> <LastName>Smith</LastName> </Name> ... <Skills> <Skill>People Skills</Skill> </Skills> <Employement /></JobCandidate>\[/code\]Where I might want to insert:Location: /JobCandidate/Skills/Technology/Database/SkillValue: MSSQLSo the after would look like:\[code\]<JobCandidate> <Name> <FirstName>Mike</FirstName> <LastName>Smith</LastName> </Name> ... <Skills> <Skill>People Skills</Skill> **<Technology> <Database> <Skill>MSSQL</Skill> </Database> </Technology>** </Skills> <Employement /></JobCandidate>\[/code\]Notice the caveat that the parent node(s) may not exist (although they are referenced in the XSD).I tried something similar to:\[quote\] UPDATE [CandidateInfoXMLDataType] SET Resume.modify(' insert element Skill {sql:variable("@SkillNew")} as last
into (/JobCandidate/SkillsTechnologySkills/Database/Skill)[1]')
WHERE JobCandidateID = @JobCandidateID\[/quote\]But it (as I expected) won't insert if the parent node doesn't already exist. No error is given either.The actual problem is related to a HIPAA 5010-837P EDI document that is translated to XML (and I have a well-defined XSD), but only the nodes with values in them are generated - but we need to append values from another data source. But I think the simplified example above will suffice.I'm looking for a SQL-only solution that hopefully doesn't require building XML from my second data source to merge.
into (/JobCandidate/SkillsTechnologySkills/Database/Skill)[1]')
WHERE JobCandidateID = @JobCandidateID\[/quote\]But it (as I expected) won't insert if the parent node doesn't already exist. No error is given either.The actual problem is related to a HIPAA 5010-837P EDI document that is translated to XML (and I have a well-defined XSD), but only the nodes with values in them are generated - but we need to append values from another data source. But I think the simplified example above will suffice.I'm looking for a SQL-only solution that hopefully doesn't require building XML from my second data source to merge.