XML Query

Question

XML Query

ljupcom

SSC Rookie

Points: 37
More actions
September 30, 2008 at 9:07 pm

#198798

I have the following,
I'd like to be able to delete
and add a new entry in its place
I've tried something like this
set @xc.modify('delete //CargoMovementsDefaults/DefaultData')
but I can't delete the element.
Please help

Viewing 6 posts - 1 through 6 (of 6 total)

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

jacob sebastian SSChampion Points: 11822 More actions · Answer 1

I am not able to see the XML data. Please replace "<>" with "[]" and post the data again. It looks like the editor does not allow XML tags.

.

ljupcom SSC Rookie Points: 37 More actions · Answer 2

[XmlSerializerSection type="PitToPort.Setup.PerUserDefaultsProfile, Setup, Version=1.0.3197.32668, Culture=neutral, PublicKeyToken=null"]

[PerUserDefaultsProfile xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"]

[CargoMovementsDefaults]

[xs:schema xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" id="NewDataSet"]

[xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:MainDataTable="CargoMovementsDefaults" msdata:UseCurrentLocale="true"]

[xs:complexType]

[xs:choice minOccurs="0" maxOccurs="unbounded"]

[xs:element name="CargoMovementsDefaults"]

[xs:complexType]

[xs:sequence]

[xs:element name="CompanyID" type="xs:int" /]

[xs:element name="MaterialID" type="xs:int" /]

[xs:element name="TerminalID" type="xs:int" /]

[xs:element name="DefaultData" type="xs:boolean" minOccurs="0" /]

[xs:element name="PlanNameID" type="xs:int" minOccurs="0" /]

[xs:element name="CargoMovementTypeID" type="xs:int" minOccurs="0" /]

[xs:element name="CargoMovementPrefix" type="xs:string" minOccurs="0" /]

[xs:element name="StockpileID_Source" type="xs:int" minOccurs="0" /]

[xs:element name="StockpileID_Dest" type="xs:int" minOccurs="0" /]

[xs:element name="MovementIntervalDays" type="xs:int" minOccurs="0" /]

[xs:element name="MovementIntervalHours" type="xs:double" minOccurs="0" /]

[xs:element name="ConsignmentSize" type="xs:double" minOccurs="0" /]

[xs:element name="ETDDelay" type="xs:double" minOccurs="0" /]

[xs:element name="QualityGroupID" type="xs:int" minOccurs="0" /]

[xs:element name="SampleCategory" type="xs:string" minOccurs="0" /]

[xs:element name="UseModelQuality" type="xs:boolean" minOccurs="0" /]

[/xs:sequence]

ljupcom SSC Rookie Points: 37 More actions · Answer 3

I'd like to be able to delete

[xs:element name="TerminalID" type="xs:int" /]

and add a new entry in its place

[xs:element name="TerminalPortID" type="xs:int" /]

I've tried something like this

set @xc.modify('delete //CargoMovementsDefaults/DefaultData')

but I can't delete the element.

Please help

jacob sebastian SSChampion Points: 11822 More actions · Answer 4

It looks like there is a problem with the XPath expression you are using. For example, to delete the specified element, you should use the complete path to reach the given element. The following example performs the delete operation you mentioned.

DECLARE @x XML

SELECT @x = '

[XmlSerializerSection type="PitToPort.Setup.PerUserDefaultsProfile, Setup, Version=1.0.3197.32668, Culture=neutral, PublicKeyToken=null"]

[PerUserDefaultsProfile xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"]

[CargoMovementsDefaults]

[xs:schema xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" id="NewDataSet"]

[xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:MainDataTable="CargoMovementsDefaults" msdata:UseCurrentLocale="true"]

[xs:complexType]

[xs:choice minOccurs="0" maxOccurs="unbounded"]

[xs:element name="CargoMovementsDefaults"]

[xs:complexType]

[xs:sequence]

[xs:element name="CompanyID" type="xs:int" /]

[xs:element name="MaterialID" type="xs:int" /]

[xs:element name="TerminalID" type="xs:int" /]

[xs:element name="DefaultData" type="xs:boolean" minOccurs="0" /]

[xs:element name="PlanNameID" type="xs:int" minOccurs="0" /]

[xs:element name="CargoMovementTypeID" type="xs:int" minOccurs="0" /]

[xs:element name="CargoMovementPrefix" type="xs:string" minOccurs="0" /]

[xs:element name="StockpileID_Source" type="xs:int" minOccurs="0" /]

[xs:element name="StockpileID_Dest" type="xs:int" minOccurs="0" /]

[xs:element name="MovementIntervalDays" type="xs:int" minOccurs="0" /]

[xs:element name="MovementIntervalHours" type="xs:double" minOccurs="0" /]

[xs:element name="ConsignmentSize" type="xs:double" minOccurs="0" /]

[xs:element name="ETDDelay" type="xs:double" minOccurs="0" /]

[xs:element name="QualityGroupID" type="xs:int" minOccurs="0" /]

[xs:element name="SampleCategory" type="xs:string" minOccurs="0" /]

[xs:element name="UseModelQuality" type="xs:boolean" minOccurs="0" /]

[/xs:sequence]

[/xs:complexType]

[/xs:element]

[/xs:choice]

[/xs:complexType]

[/xs:element]

[/xs:schema]

[/CargoMovementsDefaults]

[/PerUserDefaultsProfile]

[/XmlSerializerSection]'

SET @x.modify(' delete (XmlSerializerSection/PerUserDefaultsProfile/CargoMovementsDefaults/xs:schema/xs:element/xs:complexType/xs:choice/xs:element/xs:complexType/xs:sequence/xs:element[@name="TerminalID"])[1]')

SELECT @x

.

ljupcom SSC Rookie Points: 37 More actions · Answer 5

That is excellent, that worked well.

Thank you very much.