CODEWITHSUNDEEP
sql-servert-sql
I_G
I_G

Reputation: 413

get xml data from differents nodes and tags

Im trying to insert XML data in a temporary table

DEPARTMENT name="Administration" Revision="" IsRevision="False" Configuration="">
  <FIELD name="Name" value="Jean" type="char" />
  <FIELD name="LastName" value="Dupont" type="char" />
  <FIELD name="EmployeeID" value="5" type="float" />                  
  <ATTACHED_DOCUMENTS>
    <DOCUMENT FileName="contract.pdf" Directory="D:/Contracts"/>
  </ATTACHED_DOCUMENTS>
</DEPARTMENT>  

  insert #t_employee
(t_department,
 t_name,             
 t_value,    
 t_type)     
select *  
 from openxml (@doc, 'DEPARTMENT/FIELD')  
 with (t_department varchar(255) '../@name',
    t_name varchar(255) '@name',     
    t_value varchar(255) '@value',    
    t_type varchar(max) '@type');

This code works. But now what I want is to add a column in my table and get the information of the document in the "Attached_documents" tag.

I tried to change the path like this to add it but it didnt work

select *  
 from openxml (@doc, 'DEPARTMENT)  
 with (t_department varchar(255) '@name',
t_name varchar(255) 'FIELD/@name',     
t_value varchar(255) 'FIELD/@value',    
t_type varchar(max) 'FIELD/@type',
t_document varchar(max) 'ATTACHED_DOCUMENTS/@FileName');

How can I insert the document info in my table?

Thanks for help

Upvotes: 0

Views: 33

Answers (1)

Alan Burstein
Alan Burstein

Reputation: 7928

Since I don't have access to your XML doc I need to use a variable; hopefully this makes enough sense for you to make the required changes. 

-- your XML doc
DECLARE @doc XML =
'<DEPARTMENT name="Administration" Revision="" IsRevision="False" Configuration="">
  <FIELD name="Name" value="Jean" type="char" />
  <FIELD name="LastName" value="Dupont" type="char" />
  <FIELD name="EmployeeID" value="5" type="float" />                  
  <ATTACHED_DOCUMENTS>
    <DOCUMENT FileName="contract.pdf" Directory="D:/Contracts"/>
  </ATTACHED_DOCUMENTS>
</DEPARTMENT>'

-- Solution    
select 
  DepartmentName = FIELD.value('(../@name)[1]', 'varchar(100)'),
  FieldName      = FIELD.value('(@name)[1]',  'varchar(100)'),
  FieldTxt       = FIELD.value('(@value)[1]', 'varchar(100)'),
  FieldType      = FIELD.value('(@type)[1]', 'varchar(100)'),
  [FileName]     = info.value('(DOCUMENT/@FileName)[1]', 'varchar(100)'),
  FileDirectory  = info.value('(DOCUMENT/@Directory)[1]', 'varchar(100)')
FROM (VALUES (@doc)) t(x)
CROSS APPLY x.nodes('/DEPARTMENT/FIELD') FIELDS(FIELD)
CROSS APPLY (VALUES (@doc.query('(//DOCUMENT)'))) doc(info);

Results

DepartmentName FieldName      FieldTxt       FieldType      FileName       FileDirectory
-------------- -------------- -------------- -------------- -------------- --------------
Administration Name           Jean           char           contract.pdf   D:/Contracts
Administration LastName       Dupont         char           contract.pdf   D:/Contracts
Administration EmployeeID     5              float          contract.pdf   D:/Contracts

Upvotes: 1

Related Questions