MSSQL OpenXML problem - elements with no attributes

• msabin

  Grasshopper

  Points: 16

  More actions

  July 3, 2009 at 4:55 am

  #202023

  Hi,

  I have XML data as below. I am using OpenXML to create tabulated data.

  How can I create a packet_id column so that each proto field within the respective packet can be indentified as coming from the same packet? Note that the packet elements have no attributes.

  SELECT *

  FROM OPENXML (@idoc, '/pdml/packet/proto/field/field', 1)

  WITH (

  ProtoName varchar(max) '../../@name',

  ProtoShowName varchar(max) '../../@showname',

  ProtoSize int '../../@size',

  ProtoPos int '../../@pos',

  FieldName varchar(max)'../@name',

  FieldShowName varchar(max)'../@showname',

  FieldSize int'../@size',

  FieldPos int'../@pos',

  FieldShow varchar(max) '../@show',

  FieldValue varchar(max) '../@value',

  SubFieldName varchar(max)'@name',

  SubFieldShowName varchar(max)'@showname',

  SubFieldSize int'@size',

  SubFieldPos int'@pos',

  SubFieldShow varchar(max) '@show',

  SubFieldValue varchar(max) '@value',

  SubFieldUnmaskedValue varchar(max) '@unmaskedvalue')

• Mark Cowne

  One Orange Chip

  Points: 26983

  More actions

  July 3, 2009 at 5:16 am

  #1019713

  If you are using SQL Server 2005, you can use XQuery instead of OPENXML

  DECLARE @x XML

  SET @x=' ...your XML here'

  SELECT Packet.value('1+count(for $a in . return $a/../*[. << $a])','INT') AS packet_id,

  Proto.value('@name','VARCHAR(10)') AS proto,

  Field1.value('@name','VARCHAR(10)') AS field1,

  Field2.value('@name','VARCHAR(10)') AS field2

  FROM @x.nodes('/PDML/PACKET') AS x1(Packet)

  OUTER APPLY Packet.nodes('PROTO') AS x2(Proto)

  OUTER APPLY Proto.nodes('FIELD') AS x3(Field1)

  OUTER APPLY Field1.nodes('FIELD') AS x4(Field2)

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537