Reading dynamic XML nodes in SQL Server

Question

I have the following XML structure:

set @MailXML =
'<MailingCompany>
    <Mailman>
        <Name>Jamie</Name>
            <Age> 24 </Age>
            <Letter>
            <DestinationAddress> 440 Mountain View Parade </DestinationAddress>
            <DestinationCountry> USA </DestinationCountry>
                <OriginCountry> Australia </OriginCountry>
            <OriginAddress> 120 St Kilda Road </OriginAddress>
            </Letter>
    </Mailman>
</MailingCompany>'

My SQL currently looks like this:

-- Mail Insertion
INSERT INTO mailDB.dbo.Mailman
   SELECT
       m.value('Name[1]','varchar(50)') as Name,
       m.value('Age[1]','varchar(50)') as Age
   FROM 
       @MailXML.nodes('/MailingCompany/Mailman') as A(m)

SET @MailPersonFK = SCOPE_IDENTITY();

-- Letter Insertion
INSERT INTO mailDB.dbo.Letter
    SELECT 
        l.value('DestinationAddress[1]', 'varchar(50)') as DestinationAddress,
        l.value('DestinationCountry[1]', 'varchar(50)') as DestinationCountry,
        l.value('OriginCountry[1]', 'varchar(50)') as OriginCountry,
        l.value('OriginAddress[1]', 'varchar(50)') as OriginAddress
        @MailPersonFK as MailID
    FROM  
        @MailXML.nodes('MailingCompany/Mailman/Letter') as B(l)

I am trying to extract the Mailman and Letter data into their own respective tables. I have got that working however my issue is that the MailCompany node is dynamic. Sometimes it may be MailVehicle, for example, and I still need to read the corresponding Mailman and Letter node data and insert them into their own respective tables.

So both

FROM @MailXML.nodes('/MailingCompany/Mailman') as A(t)

and

FROM @MailXML.nodes('MailingCompany/Mailman/Letter') as B(l)

Will need to be changed to allow MailingCompany to be dynamic.

I have tried to extract the parent node and concatenate it into a string to put into the .nodes function like the following:

set @DynXML = '/' + @parentNodeVar + '/Mailman'

FROM @MailXML.nodes(@DynXML) as A(t)

However I get the following error:

The argument 1 of the XML data type method "nodes" must be a string literal.

How can I overcome this dynamic XML issue?

Thank you very much in advance

as per my understanding , you trying to say that Mailman and Letter is never changes but parent node of Mailman may be change . so you have difficulty to get data of Mailman and Letter .means you want to find Mailman and Letter node dynamically from xml .Am iI Right ? — Ronak Patel
– Ronak Patel, Commented Feb 7, 2017 at 6:22

Gottfried Lesigang · Accepted Answer · 2017-02-07 21:57:26Z

3

Look at this reduced example:

DECLARE @xml1 XML=
N'<MailingCompany>
  <Mailman>
    <Name>Jamie</Name>
    <Letter>
      <DestinationAddress> 440 Mountain View Parade </DestinationAddress>
    </Letter>
  </Mailman>
</MailingCompany>';

DECLARE @xml2 XML=
N'<OtherName>
  <Mailman>
    <Name>Jodie</Name>
    <Letter>
      <DestinationAddress> This is the other address </DestinationAddress>
    </Letter>
  </Mailman>
</OtherName>';

SELECT @xml1.value(N'(*/Mailman/Name)[1]','nvarchar(max)') AS Mailman_Name
      ,@xml1.value(N'(*/Mailman/Letter/DestinationAddress)[1]','nvarchar(max)') AS DestinationAddress
SELECT @xml2.value(N'(*/Mailman/Name)[1]','nvarchar(max)') AS Mailman_Name
      ,@xml2.value(N'(*/Mailman/Letter/DestinationAddress)[1]','nvarchar(max)') AS DestinationAddress

You can replace a node's name with *.

Another trick is the deep search with // (same result as before):

SELECT @xml1.value(N'(//Name)[1]','nvarchar(max)') AS Mailman_Name
      ,@xml1.value(N'(//DestinationAddress)[1]','nvarchar(max)') AS DestinationAddress
SELECT @xml2.value(N'(//Name)[1]','nvarchar(max)') AS Mailman_Name
      ,@xml2.value(N'(//DestinationAddress)[1]','nvarchar(max)') AS DestinationAddress

The general rule: Be as specific as possible.

answered Feb 7, 2017 at 21:57

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

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

2 Comments

Mark Over a year ago

Awesome that's exactly what I wanted. Thank you @Shnugo!

Kunal Over a year ago

I have the exact same problem but i need to select also the "OtherName"... how can i do this? Thanks @Shnugo

Collectives™ on Stack Overflow

Reading dynamic XML nodes in SQL Server

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related