Search code examples
sql-serverxmlsqlxml

Reading XML data from SQL Server with Hear and Node values

I have the following XML data in a file "Test1.xml":

<TextValuess>
 <TextValues Name="Value1" Override="true" Type="String">
  <DEV>Source=DEV;Catalog=DEV_DMT;Integrated Security=SSPI;</DEV>
  <INT10>Source=LAB;Catalog=TST_INT10;Integrated Security=SSPI;</INT10>
  <INT>Source=LAB1;Catalog=TST_INT1;Integrated Security=SSPI;</INT>
  <INT2>Source=LAB10;Catalog=TST_INT12;Integrated Security=SSPI;</INT2>
 </TextValues>
 <TextValues Name="ENVIRONMENT" Override="true" Type="String">
  <DEV>DEV</DEV>
  <INT10>INT10</INT10>
  <INT>INT1</INT>
  <INT2>INT15</INT2>
 </TextValues>
</TextValuess>

I am trying to read the INT10 values and Name of the <TextValues> tag. I need output like below in SQL Server:

Name               Value
----               -----
Value1             LAB
Environment        INT10

I have tried with these SQL statements. I was able to get either the Name value or the INT10 values.

Statement 1:

select c3.value('INT10[1]','varchar(50)')
from
    (select cast(c1 as xml)
     from OPENROWSET (BULK 'D:\Tasks\Test1.xml',SINGLE_BLOB) as T1(c1)) as T2(c2)
cross apply c2.nodes('/TextValuess/TextValues') T3(c3)

With this I was able to retrieve the values for INT10

Statement 2:

 DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)

 SELECT @XML = ' <TextValuess>
 <TextValues Name="Value1" Override="true" Type="String">
  <DEV>Source=DEV;Catalog=DEV_DMT;Integrated Security=SSPI;</DEV>
  <INT10>Source=LAB;Catalog=TST_INT10;Integrated Security=SSPI;</INT10>
  <INT>Source=LAB1;Catalog=TST_INT1;Integrated Security=SSPI;</INT>
  <INT2>Source=LAB10;Catalog=TST_INT12;Integrated Security=SSPI;</INT2>
 </TextValues>
 <TextValues Name="ENVIRONMENT" Override="true" Type="String">
  <DEV>DEV</DEV>
  <INT10>INT10</INT10>
  <INT>INT1</INT>
  <INT2>INT15</INT2>
 </TextValues>
</TextValuess>'

EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML

SELECT Name ,INT10
FROM OPENXML(@hDoc, 'TextValuess/TextValues/INT10')
WITH 
(
Name [varchar](50) '../@Name',
INT10 [varchar](100) '../@INT10'
)

With this I was able to retrieve only Name Information but not the INT10 Value.

Solution

  • Try this code:

    -- declare a XML variable
DECLARE @XmlInput XML;

-- load the XML from the file into that XML variable
SELECT @XmlInput = CAST(c1 AS XML)
FROM OPENROWSET (BULK 'D:\Tasks\Test1.xml',SINGLE_BLOB) AS T1(c1)

-- extract the "Name" attribute and "INT10" element from the XML    
SELECT
    Name = XC.value('@Name', 'varchar(50)'),
    Int10Value = XC.value('(INT10)[1]', 'varchar(100)')
FROM    
    @XmlData.nodes('/TextValuess/TextValues') AS XT(XC)

    The call to .nodes() using the built-in, much preferred XQuery functionality (dump the OPENXML stuff - it's old and legacy and has memory leaks - XQuery is much easier to use, too!) returns a list of XML fragments - one for each match of the XPath expression in your document (here: one for each <TextValues> node under the root).

    Then you reach into that XML fragment, and extract the name attribute (using the @Name expression), and the first (and only) <INT10> sub-element and convert those to "regular" values (with a datatype defined by the second parameter of the .value() call)