xml column from table

• astrid 69000

  Hall of Fame

  Points: 3208

  More actions

  February 23, 2016 at 4:11 am

  #325051

  Hi,

  I have a table who have a number of columns. One of the columns contains an xml.

  How can I parse the xml into column using the origin table as the source?

  I saw a lot of samples, but all of them refer at the xml that is set at the beginning of the sample. :ermm:

  Thanks!!!

  Astrid

• Phil Parkin

  SSC Guru

  Points: 246960

  More actions

  February 23, 2016 at 5:59 am

  #1860370

  astrid 69000 (2/23/2016)

  ---

  Hi,

  I have a table who have a number of columns. One of the columns contains an xml.

  How can I parse the xml into column using the origin table as the source?

  I saw a lot of samples, but all of them refer at the xml that is set at the beginning of the sample. :ermm:

  Thanks!!!

  Astrid

  Do you know the structure of the XML in advance?

  What are you intending to do with the 'parsed' XML?

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin

• astrid 69000

  Hall of Fame

  Points: 3208

  More actions

  February 23, 2016 at 6:54 am

  #1860386

  Hi,

  I take data from different xml therefore the structure may vary, and I need to bring it to a table so the data science can run a statistic model on it.

  thanks

• Phil Parkin

  SSC Guru

  Points: 246960

  More actions

  February 23, 2016 at 7:07 am

  #1860391

  astrid 69000 (2/23/2016)

  ---

  Hi,

  I take data from different xml therefore the structure may vary, and I need to bring it to a table so the data science can run a statistic model on it.

  thanks

  If you don't know the structure of the source, how can you ever map it to a fixed destination?

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin

• astrid 69000

  Hall of Fame

  Points: 3208

  More actions

  February 23, 2016 at 7:18 am

  #1860398

  you can tell the code to run the nodes. no?

  for example, if you have 5 nodes and one is added, it will add the column

• Phil Parkin

  SSC Guru

  Points: 246960

  More actions

  February 23, 2016 at 7:23 am

  #1860401

  astrid 69000 (2/23/2016)

  ---

  you can tell the code to run the nodes. no?

  for example, if you have 5 nodes and one is added, it will add the column

  OK, maybe this works best if you provide an example of an XML doc and how you would like the output to appear.

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin

• astrid 69000

  Hall of Fame

  Points: 3208

  More actions

  February 23, 2016 at 7:49 am

  #1860406

  <xml-response>

  <tracking-number>2wvbf8fvxs</tracking-number>

  <action>Deny</action>

  <deny-codes>37</deny-codes>

  <deny-descriptions>(37) Deny when applicant is under21.</deny-descriptions>

  <exception-descriptions nil="true"/>

  <clear-products-request>

  <account-name>Main corporate account</account-name>

  <control-file-name>Default</control-file-name>

  <control-file-substituted>Clear Control file version not specified; using most recent approved version.</control-file-substituted>

  <control-file-version-number>6</control-file-version-number>

  <group-name>Tampa Check Cashing</group-name>

  <location-name>Clearwater Branch</location-name>

  <username>gregoryhines</username>

  </clear-products-request>

  </xml-response>

  Hi,

  this is a small part of the xml, the whole xml bring 1500 nodes, which i know i will also need to separate..

  thanks

• Alan Burstein

  SSC Guru

  Points: 61141

  More actions

  February 23, 2016 at 8:36 am

  #1860434

  Note my code and comments.

  USE tempdb

  GO

  -- Create a table with an XML column

  IF OBJECT_ID('tempdb.dbo.YourTable') IS NOT NULL DROP TABLE dbo.YourTable;

  CREATE TABLE dbo.YourTable(SomeID int identity primary key, SomeXML xml NOT NULL);

  -- add two sample records

  INSERT dbo.YourTable VALUES

  ('<xml-response>

  <tracking-number>2wvbf8fvxs</tracking-number>

  <action>Deny</action>

  <deny-codes>37</deny-codes>

  <deny-descriptions>(37) Deny when applicant is under21.</deny-descriptions>

  <exception-descriptions nil="true"/>

  <clear-products-request>

  <account-name>Main corporate account</account-name>

  <control-file-name>Default</control-file-name>

  <control-file-substituted>Clear Control file version not specified; using most recent approved version.</control-file-substituted>

  <control-file-version-number>6</control-file-version-number>

  <group-name>Tampa Check Cashing</group-name>

  <location-name>Clearwater Branch</location-name>

  <username>gregoryhines</username>

  </clear-products-request>

  </xml-response>'),

  ('<xml-response>

  <tracking-number>xxxyyyzzz</tracking-number>

  <action>Deny</action>

  <deny-codes>50</deny-codes>

  <deny-descriptions>(37) Deny when applicant is drunk.</deny-descriptions>

  <exception-descriptions nil="False"/>

  <clear-products-request>

  <account-name>Main corporate account</account-name>

  <control-file-name>Default</control-file-name>

  <control-file-substituted>Blah blah blah.</control-file-substituted>

  <control-file-version-number>6</control-file-version-number>

  <group-name>Tampa Check Cashing</group-name>

  <location-name>Clearwater Branch</location-name>

  <username>gregoryhines</username>

  </clear-products-request>

  </xml-response>');

  -- Examples of how to extract some of the XML values

  SELECT

  SomeID, -- a unique key to determine which row the XML data was extracted from

  x.value('tracking-number[1]', 'varchar(100)') AS trackingNumber,

  x.value('action[1]', 'varchar(100)') AS ActionTxt,

  x.value('(clear-products-request/username[1]/text())[1]', 'varchar(100)') AS UserName,

  x.value('(exception-descriptions/@nil)[1]', 'varchar(100)') AS IsNil -- Example of getting an attribute

  FROM dbo.YourTable t

  CROSS APPLY t.SomeXML.nodes('/xml-response') tx(x);

  -- above is an example of how to use the nodes method to set XML context and read the xml file

  -- see https://msdn.microsoft.com/en-us/library/ms190798.aspx (XML data type methods)

  --DROP TABLE dbo.YourTable

  GO

  To read the XML data type in SQL server you need a basic understanding of CROSS APPLY, the XML data type methods (link in my code above) and XPATH which you can learn more about here.

  "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

  -- Itzik Ben-Gan 2001

• astrid 69000

  Hall of Fame

  Points: 3208

  More actions

  February 23, 2016 at 8:56 am

  #1860446

  thanks so much for the help!:-)