XML data to SQL Server table

Question

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

It is of no importance out of which <data> node your numbers are taken? — Gottfried Lesigang
– Gottfried Lesigang, Commented Sep 18, 2018 at 6:10

marc_s · Accepted Answer · 2018-09-18 04:25:31Z

2

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.

answered Sep 18, 2018 at 4:25

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

gsva Over a year ago

Yes, they are numbers here in my example. But they are alphanumeric in some cases. The example was simplified so that it could be clear to everyone. Thank you for your help!!

Gottfried Lesigang · Accepted Answer · 2018-09-18 06:18:03Z

2

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

answered Sep 18, 2018 at 6:18

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

1 Comment

gsva Over a year ago

Thank you for your response. Yes, order was not important

Collectives™ on Stack Overflow

XML data to SQL Server table

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related