Split XML variable, join and insert into a temp table XML column

Question

I have an XML variable that contains the following XML:

<Fruits>
  <Fruit>
    <Type>Apple</Type>
    <FruitID>1</FruitID>
    <MoreInfo>
      <Info Name="GreenApples" Value="3900" />
    </MoreInfo>
  </Fruit>
  <Fruit>
    <Type>Orange</Type>
    <FruitID>2</FruitID>
    <MoreInfo>
      <Info Name="Oranges" Value="1100" />
    </MoreInfo>
  </Fruit>
</Fruits>

I am trying to create a temp table that looks like that:

CREATE TABLE #XmlTable (
    email nvarchar(100),
    xmlNode XML
)

The output that I am aiming for is:

email | xmlNode

[email protected] | <Fruit><Type>Apple</Type><FruitID>1</FruitID><MoreInfo><Info Name="GreenApples" Value="3900" /></MoreInfo></Fruit>
[email protected] | <Fruit><Type>Orange</Type><FruitID>2</FruitID><MoreInfo><Info Name="Oranges" Value="1100" /></MoreInfo></Fruit>

The problem is that I don't know how to convert it to XML when I split it and insert it in the table.

I have the following SQL that is doing the job, but how do I convert it to XML?

INSERT INTO #XmlTable
SELECT  EOR.email, 
        xmlNode.Col.value('.', 'nvarchar(max)') AS XML
FROM @outputXML.nodes('/Fruits') xmlNode(Col)
CROSS APPLY @outputXML.nodes('/Fruits/Fruit/FruitID') xmlValue(i)
INNER JOIN #EmailsOfReceivers EOR
ON EOR.ID= xmlValue.i.value('.','nvarchar(max)')

It works fine, but of course the xmlNode column doesn't have the XML tags.

har07 · Accepted Answer · 2015-05-22 08:48:01Z

2

Here is one possible way :

INSERT INTO #XmlTable
SELECT  EOR.email, 
        fruit.x.query('.') AS XML
FROM @outputXML.nodes('/Fruits/Fruit') fruit(x)
INNER JOIN #EmailsOfReceivers EOR
ON EOR.ID = fruit.x.value('FruitID[1]','nvarchar(max)')

Fiddle demo

Basically, shred the XML on <Fruit> nodes, then you can use query() instead of value() to get the XML tags.

Side note: I'd return FruitID as int instead of nvarchar(max), if possible.

answered May 22, 2015 at 8:48

har07

89.5k12 gold badges87 silver badges143 bronze badges

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

5 Comments

har07 Over a year ago

@Apostrofix yes, it necessary to guarantee that there is maximum one element returned. There will be an error if you remove it.

Apostrofix Over a year ago

What If the whole XML is wrapped in a ROOT tag? Would it be possible to get the Fruit nodes and do the join?

har07 Over a year ago

Not sure I understand this new problem you're asking now. If the entire XML structure remains the same other than the ROOT tag, simply change /Fruits/Fruit to /ROOT/Fruits/Fruit ..

Apostrofix Over a year ago

Yes, that's exactly what I mean. But then the output is like: <Fruit><Type>Orange</Type><FruitID.... - Can I wrap it in the <Fruits> tag too?

har07 Over a year ago

like '<Fruits>' + CAST(fruit.x.query('.') AS varchar(max)) + '</Fruits>' ?

Krishnraj Rana · Accepted Answer · 2015-05-22 09:09:48Z

1

You can use .query('.') for your xml column:

DECLARE @outputXML XML

SELECT @outputXML = '<Fruits>
  <Fruit>
    <Type>Apple</Type>
    <FruitID>1</FruitID>
    <MoreInfo>
      <Info Name="GreenApples" Value="3900" />
    </MoreInfo>
  </Fruit>
  <Fruit>
    <Type>Orange</Type>
    <FruitID>2</FruitID>
    <MoreInfo>
      <Info Name="Oranges" Value="1100" />
    </MoreInfo>
  </Fruit>
</Fruits>'

INSERT INTO #XmlTable
SELECT  EOR.email, 
        xmlNode.Col.query('.') AS MyXML
FROM @outputXML.nodes('/Fruits/Fruit') xmlNode(Col)
INNER JOIN #EmailsOfReceivers EOR
ON EOR.ID = xmlNode.Col.value('FruitID[1]','nvarchar(max)')

edited May 22, 2015 at 9:09

answered May 22, 2015 at 8:49

Krishnraj Rana

6,6583 gold badges33 silver badges37 bronze badges

Collectives™ on Stack Overflow

Split XML variable, join and insert into a temp table XML column

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related