XML Parse

• hardikbeit

  SSC Rookie

  Points: 32

  More actions

  April 11, 2020 at 8:52 pm

  #3742237

  I would like to parse XML data which is in table.

  Declare @abc table
  ( id varchar(20),
  address xml)
  
  insert into @abc
  values('Qa30W00003abc', 
  '<latitude xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="urn:partner.soap.sforce.com" xsi:nil="true" />
  <longitude xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="urn:partner.soap.sforce.com" xsi:nil="true" />
  <city xmlns="urn:partner.soap.sforce.com">Portland</city>
  <country xmlns="urn:partner.soap.sforce.com">USA</country>
  <countryCode xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="urn:partner.soap.sforce.com" xsi:nil="true" />
  <postalCode xmlns="urn:partner.soap.sforce.com">12345</postalCode>
  <state xmlns="urn:partner.soap.sforce.com">OR</state>
  <stateCode xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="urn:partner.soap.sforce.com" xsi:nil="true" />
  <street xmlns="urn:partner.soap.sforce.com">200 Columbia Street</street>'),
  ('Qa30W00003bcd',
  '<latitude xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="urn:partner.soap.sforce.com" xsi:nil="true" />
  <longitude xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="urn:partner.soap.sforce.com" xsi:nil="true" />
  <city xmlns="urn:partner.soap.sforce.com">Lexington</city>
  <country xmlns="urn:partner.soap.sforce.com">US</country>
  <countryCode xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="urn:partner.soap.sforce.com" xsi:nil="true" />
  <postalCode xmlns="urn:partner.soap.sforce.com">23451</postalCode>
  <state xmlns="urn:partner.soap.sforce.com">South Carolina</state>
  <stateCode xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="urn:partner.soap.sforce.com" xsi:nil="true" />
  <street xmlns="urn:partner.soap.sforce.com">20 Ann Avenue</street>')
  
  Select * from @abc

  How I can retrieve the values like Id, Street, City, State as Column for SQL Query result?

  I have tried Address.nodes('*') m(c) but no luck. Can you please help?

• Eddie Wuerch

  SSChampion

  Points: 12535

  More actions

  April 12, 2020 at 5:54 pm

  #3742302

  First, you need to deal with the XML Namespaces xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" and xmlns="urn:partner.soap.sforce.com" :

  WITH XMLNAMESPACES ('http://www.w3.org/2001/XMLSchema-instance' AS xsi, DEFAULT 'urn:partner.soap.sforce.com')

  Then you can shred each row's address XML column using CROSS APPLY and the XML Nodes function:

  WITH XMLNAMESPACES ('http://www.w3.org/2001/XMLSchema-instance' AS xsi, DEFAULT 'urn:partner.soap.sforce.com')
  SELECT [...]
    FROM @abc abc CROSS APPLY
         abc.[address].nodes('.') as nds(a)
  

  From there, you can pull out each element's value:

  Declare @abc table
  ( id varchar(20),
  address xml)
  
  insert into @abc
  values('Qa30W00003abc', 
  '<latitude xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="urn:partner.soap.sforce.com" xsi:nil="true" />
  <longitude xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="urn:partner.soap.sforce.com" xsi:nil="true" />
  <city xmlns="urn:partner.soap.sforce.com">Portland</city>
  <country xmlns="urn:partner.soap.sforce.com">USA</country>
  <countryCode xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="urn:partner.soap.sforce.com" xsi:nil="true" />
  <postalCode xmlns="urn:partner.soap.sforce.com">12345</postalCode>
  <state xmlns="urn:partner.soap.sforce.com">OR</state>
  <stateCode xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="urn:partner.soap.sforce.com" xsi:nil="true" />
  <street xmlns="urn:partner.soap.sforce.com">200 Columbia Street</street>'),
  ('Qa30W00003bcd',
  '<latitude xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="urn:partner.soap.sforce.com" xsi:nil="true" />
  <longitude xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="urn:partner.soap.sforce.com" xsi:nil="true" />
  <city xmlns="urn:partner.soap.sforce.com">Lexington</city>
  <country xmlns="urn:partner.soap.sforce.com">US</country>
  <countryCode xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="urn:partner.soap.sforce.com" xsi:nil="true" />
  <postalCode xmlns="urn:partner.soap.sforce.com">23451</postalCode>
  <state xmlns="urn:partner.soap.sforce.com">South Carolina</state>
  <stateCode xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="urn:partner.soap.sforce.com" xsi:nil="true" />
  <street xmlns="urn:partner.soap.sforce.com">20 Ann Avenue</street>');
  
  
  WITH XMLNAMESPACES ('http://www.w3.org/2001/XMLSchema-instance' AS xsi, DEFAULT 'urn:partner.soap.sforce.com')
  SELECT abc.id,
         a.value('(/latitude)[1]',   'varchar(128)') AS [latitude],
         a.value('(/longitude)[1]',  'varchar(128)') AS [longitude],
         a.value('(/city)[1]',       'varchar(128)') AS [city],
         a.value('(/country)[1]',    'varchar(128)') AS [country],
         a.value('(/postalCode)[1]', 'varchar(16)')  AS [postalCode],
         a.value('(/state)[1]',      'varchar(128)') AS [state],
         a.value('(/stateCode)[1]',  'varchar(128)') AS [stateCode],
         a.value('(/street)[1]',     'varchar(128)') AS [street],
         a.query('.') as FullNode
    FROM @abc abc CROSS APPLY
         abc.[address].nodes('.') as nds(a)
  

  A couple notes:

  1. The value in the @abc.address value is not a well-formed XML fragment. It has no top-level element.
  2. None of the elements use the namespaces, but they are present and must be addressed in the query.

  If you have control over generation of the XML data, remove the namespaces and add a top-level element. It makes querying much cleaner.

  Eddie Wuerch
  MCM: SQL