2

I am wondering how to split and parse long XML values into one row. I have seen some options on this site for splitting via ',' but that won't work for me, since there are no commas in my field. For reference the data would look something like:

[XMLColumn]
<Student><Id>uniqueidentifier</Id><Name>Chris</Name><Grade>11</Grade></Student><Student><Id>uniqueidentifier</Id><Name>Joe</Name><Grade>4</Grade></Student><Student><Id>uniqueidentifier</Id><Name>Alex</Name><Grade>9</Grade></Student><Student><Id>uniqueidentifier</Id><Name>Mary</Name><Grade>2</Grade></Student>

I would like to split on each Student tag (<Student></Student>) So it might look something like:

Id                    Name        Grade
uniqueidentifier      Chris        11
uniqueidentifier      Joe          4
uniqueidentifier      Alex         9
uniqueidentifier      Mary         2

Also ideally, I do not want to use a function unless it's of a low complexity. I am just doing experiments on existing school projects that I have done and seeing if there are ways to make my queries faster.

The problem is, although I will always know the properties for a Student (Id, Name, and Grade), the data inside will always be of different length, so even splitting at a certain index won't necessarily work. If anyone could help, that'd be great.

  • You seem to be unaware that T-SQL has built-in support for XML, so no string splitting of any kind is necessary. Cast your value to `XML` and methods like `nodes` and `values` become available. See [here](https://stackoverflow.com/q/14712864/4137916), for example. – Jeroen Mostert Jul 03 '18 at 12:44
  • I think my biggest issue is figuring out how to DECLARE a SELECT * with XML. I can only grab 1 record when I do something like DECLARE @myXML XML = SELECT Data FROM Student –  Jul 03 '18 at 13:05
  • 1
    `CROSS APPLY` is your friend there. `SELECT x.value('Id[1]', 'uniqueidentifier'), ... FROM Student CROSS APPLY Data.nodes('Student') AS S(x)`. – Jeroen Mostert Jul 03 '18 at 13:34
  • Thank you, this was helpful. –  Jul 03 '18 at 15:49

1 Answers1

0

Convert the data into XML and use xQuery to shred the XML object/document.

Declare @XML XML = N'<Student>
                          <Id>uniqueidentifier</Id>
                          <Name>Chris</Name>
                          <Grade>11</Grade>
                       </Student>
                       <Student>
                          <Id>uniqueidentifier</Id>
                          <Name>Joe</Name>
                          <Grade>4</Grade>
                       </Student>
                       <Student>
                          <Id>uniqueidentifier</Id>
                          <Name>Alex</Name>
                          <Grade>9</Grade>
                       </Student>
                       <Student>
                          <Id>uniqueidentifier</Id>
                          <Name>Mary</Name>
                          <Grade>2</Grade>
                       </Student>'

SELECT
      X.StockData.query('Id').value('.','VARCHAR(100)')     AS [Id]
    , X.StockData.query('Name').value('.','VARCHAR(100)')   AS [Name]
    , X.StockData.query('Grade').value('.','INT')           AS [Grade]

FROM @XML.nodes('Student') AS X(StockData)

Output

╔══════════════════╦═══════╦═══════╗
║        Id        ║ Name  ║ Grade ║
╠══════════════════╬═══════╬═══════╣
║ uniqueidentifier ║ Chris ║    11 ║
║ uniqueidentifier ║ Joe   ║     4 ║
║ uniqueidentifier ║ Alex  ║     9 ║
║ uniqueidentifier ║ Mary  ║     2 ║
╚══════════════════╩═══════╩═══════╝
M.Ali
  • 67,945
  • 13
  • 101
  • 127
  • Nice, but what happens if the XML data is a massive XML string and the user does not want to DECLARE a hard to read variable? –  Jul 03 '18 at 12:57
  • @Darnold14 would you like to expand on "Massive" and "Hard to Read"? – M.Ali Jul 03 '18 at 13:24
  • Well, just suppose I have 1000 Student Objects. Could there be a way, for brevity, to not have to explicitly write that all out but condense it to a single statement for entire retrieval? –  Jul 03 '18 at 13:26
  • XML shredding is best done in the application layer, SQL Server is not the best place to handle xml data, however 1000 students is pretty small size and SQL Server should be able to handle it quite well unless you have a 2GB Ram and 2 core server :) – M.Ali Jul 03 '18 at 13:29
  • That's fair, but is there a way to replace the N' declaration with maybe a statement to get the entire XML string for every Student object, just for scalability –  Jul 03 '18 at 13:31
  • @Darnold14 I am not sure exactly what you are asking here but even with the Declare statement I have managed to get multiple students processed in one go. Your code (Stored Procedure) could have a parameter of XML data type and you can pass the whole document to that one variable from your application, the whole document will get processed with one execution. I am not sure if I have understood your question properly? – M.Ali Jul 03 '18 at 13:39
  • Sorry, your solution works correctly. I guess I am just thinking, what if the user doesn't know the data beforehand and there's thousands of rows to process? –  Jul 03 '18 at 13:41