OpenXML

• Amit Lohia

  SSCertifiable

  Points: 6104

  More actions

  September 5, 2006 at 10:44 am

  #69240

   

  This is a sample of my XML

  <ShipmentHeader SettleFlag="A">

  <Customer CustomerID="A" CustomerName="A"/>

  <SubCustomer SubCustomerID="A" SubCustomerName="A"/>

  </

  ShipmentHeader>

  and I want the following output as in single openXML command.

  SettleFlag CustomerId CustomerName SubCustomerId SubCustomerName

  Is this possible and if yes then how.

  The following query do not give me the value for CustomerId

  SELECT

  *

  FROM

  OPENXML( @docHandle,'/ShipmentHeader')

  WITH

  (

  [SettleFlag]

  varchar(100)

  ,CustomerID varchar(100) '/Customer/@CustomerID'

  ) X

  ---

  Kindest Regards,

  Amit Lohia

• Bert De Haes

  Hall of Fame

  Points: 3091

  More actions

  September 6, 2006 at 12:05 am

  #658703

  Strange XML ... But you can  do it like this :

   

  SELECT *

  FROM   OPENXML (@docHandle, '/ShipmentHeader',2)

           WITH (SettleFlag      char(1)      '@SettleFlag',

                 CustomerID      varchar(10)  'Customer/@CustomerID',

                 CustomerName    varchar(10)  'Customer/@CustomerName',

                 SubCustomerID   varchar(10)  'SubCustomer/@SubCustomerID',

                 SubCustomerName varchar(10)  'SubCustomer/@SubCustomerName' )

• reachven

  Valued Member

  Points: 70

  More actions

  October 26, 2007 at 8:30 am

  #745750

  Hi Bert, I have a simlar question,except in my case, i have more than one subcustomer.

  ShipmentHeader SettleFlag="A">

  Customer CustomerID="A" CustomerName="A"/>

  SubCustomer SubCustomerID="A" SubCustomerName="A"/>

  SubCustomer SubCustomerID="B" SubCustomerName="B"/>

  /ShipmentHeader>

  Note: (Modified the above XML by deleting preceding "<" symbol)

  If i set the node to the top level as you suggested, with your code will i be able to get both subcustomers.

  Thanks in Advance

  Venkat

• Bert De Haes

  Hall of Fame

  Points: 3091

  More actions

  October 26, 2007 at 10:58 am

  #745830

  [font="Courier New"]I suppose there is only 1 customer, and 1 or more subcustoerms. This query would do the job :

  DECLARE @docHandle int

  DECLARE @doc varchar(1000)

  SET @doc ='

  <ShipmentHeader SettleFlag="A">

  <Customer CustomerID="A1" CustomerName="A"/>

  <SubCustomer SubCustomerID="AA" SubCustomerName="An"/>

  <SubCustomer SubCustomerID="BB" SubCustomerName="Bn"/>

  <SubCustomer SubCustomerID="CC" SubCustomerName="Cn"/>

  </ShipmentHeader>'

  EXEC sp_xml_preparedocument @docHandle OUTPUT, @doc

  select C.*,S.*

  from

  (

  SELECT *

  FROM OPENXML(@docHandle, '/ShipmentHeader/Customer',2)

  WITH(SettleFlag char(1) '../@SettleFlag',

  CustomerID varchar(10) './@CustomerID',

  CustomerName varchar(10) './@CustomerName' )

  ) C

  full outer join

  (

  SELECT *

  FROM OPENXML(@docHandle, '//SubCustomer',2)

  WITH(SubCustomerID varchar(10) '@SubCustomerID',

  SubCustomerName varchar(10) '@SubCustomerName' )

  ) S on ( 1=1)

  But if you could change the xml like this, the query becomes much simplier :

  DECLARE @docHandle int

  DECLARE @doc varchar(1000)

  SET @doc ='

  <ShipmentHeader SettleFlag="A">

  <Customer CustomerID="A1" CustomerName="A">

  <SubCustomer SubCustomerID="AA" SubCustomerName="An"/>

  <SubCustomer SubCustomerID="BB" SubCustomerName="Bn"/>

  <SubCustomer SubCustomerID="CC" SubCustomerName="Cn"/>

  </Customer>

  </ShipmentHeader>'

  EXEC sp_xml_preparedocument @docHandle OUTPUT, @doc

  SELECT *

  FROM OPENXML(@docHandle, '/ShipmentHeader/Customer/SubCustomer',2)

  WITH(SettleFlag char(1) '../../@SettleFlag',

  CustomerID varchar(10) '../@CustomerID',

  CustomerName varchar(10) '../@CustomerName',

  SubCustomerID varchar(10) '@SubCustomerID',

  SubCustomerName varchar(10) '@SubCustomerName' )[/font]