CODEWITHSUNDEEP
sql-serverxmlt-sql
gsva
gsva

Reputation: 45

XML data to SQL Server table

I have looked online for transforming XML data to table using T-SQL. I only see part of the answer. My XML is a little different. Can someone help me with the following.

DECLARE @XmlData XML

SELECT @XmlData = '<header>
  <data>
    <numbersData>
      <numbers>
        <number>1</number>
        <number>2</number>
      </numbers>
    </numbersData>
  </data>
  <data>
    <numbersData>
      <numbers>
        <number>3</number>
        <number>4</number>
      </numbers>
    </numbersData>
  </data>
</header>'

Query:

SELECT 
    Numbers.Data.value('(number)[1]', 'VARCHAR(100)') AS Num
FROM
    @XmlData.nodes('/header/data/numbersData/numbers') Numbers(Data)

The result of this:

Num
----
1
3

Notice, how it only returns 1 and 3. 2 and 4 are missing.

Unfortunately, this file is coming from client and they will not change the format. I am trying to get the following result

Num
---
1
2
3
4

Upvotes: 3

Views: 87

Answers (2)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

If you really need nothing but the numbers you've got answers already.

The following answer will additionally let you know, from which <data> node the number was taken:

WITH theData AS
(
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS DataIndex
          ,d.query('.') DataNode
    FROM @XmlData.nodes('/header/data') A(d)
)
SELECT d.DataIndex
      ,n.value('text()[1]','int') AS TheNumber
FROM theData d
CROSS APPLY d.DataNode.nodes('data/numbersData/numbers/number') A(n);

The result

DataIndex   TheNumber
1           1
1           2
2           3
2           4

Upvotes: 2

marc_s
marc_s

Reputation: 754478

Why are you converting those values which are clearly numbers into VARCHAR(100)? Doesn't make any sense....

Anyway - try this SELECT statement:

SELECT 
    Data.value('.', 'INT') AS Num
FROM
    @XmlData.nodes('/header/data/numbersData/numbers/number') Numbers(Data)

The XPath basically "drills down" all the way to the <number> XML elements, and thus gets a complete list of all those elements, and converts their value to INT - this outputs the desired four numbers.

Upvotes: 2

Related Questions