0

I have an XML column in a MS SQL Server 2012 database that I need to create a query for that extracts the nodes into a table. Here's a gist of the XML structure:

<root>
  <ProjectInfo>
  <PrimaryContact>
    <Name>
    <Phone>
    <Email>
  </PrimaryContact>
  <SecondaryContact>
    <Name>
    <Phone>
    <Email>
  </SecondaryContact>
  <TechnicalContact>
    <Name>
    <Phone>
    <Email>
  </TechnicalContact>
  <BillingContact>
    <Name>
    <Phone>
    <Email>
  </BillingContact>
  <OtherStuff>
</root>

I'm trying to write a query that get's the Name, Phone, and Email from each Contact node. The XPath /root/*Contact/ isn't legal unfortunately. I know that I can write a query that combines a bunch of UNIONs to merge the columns, but I feel like there is probably a simpler way that I am not currently aware.

Is there a way to use wildcards or some sort of OR-ing mechanism that can be used to retrieve the Name, Phone, & Email from each of the *Contact nodes?

Note: I cannot use /root/*/Name because there are other nodes that have Name as the next inner node that aren't for contacts, and Phone & Email are both optional fields.

JNYRanger
  • 6,829
  • 12
  • 53
  • 81

1 Answers1

1

You might have a look on this working example:

DECLARE @xml XML=
'<root>
  <ProjectInfo>
    <Name value="this not"/>
  </ProjectInfo>
  <PrimaryContact>
    <Name value="x"/>
    <Phone value="y"/>
    <Email value="z"/>
  </PrimaryContact>
  <SecondaryContact>
    <Name value="a"/>
    <Phone value="b"/>
    <Email value="c"/>
  </SecondaryContact>
  <TechnicalContact>
    <Name value="e"/>
    <Phone value="f"/>
    <Email value="g"/>
  </TechnicalContact>
  <BillingContact>
    <Name value="m"/>
    <Phone value="n"/>
    <Email value="o"/>
  </BillingContact>
  <OtherStuff>
    <Name value="don''t include"/>
  </OtherStuff>
</root>';

SELECT Level1.value('local-name(.)','nvarchar(max)') AS Level1_Name
      ,Level2.value('local-name(.)','nvarchar(max)') AS Level2_Name
      ,Level2.value('@value','nvarchar(max)') AS Level2_Value
FROM @xml.nodes('/root/*[fn:contains(local-name(),"Contact")]') A(Level1)
CROSS APPLY Level1.nodes('*') AS B(Level2);

The result

+------------------+-------+---+
| PrimaryContact   | Name  | x |
+------------------+-------+---+
| PrimaryContact   | Phone | y |
+------------------+-------+---+
| PrimaryContact   | Email | z |
+------------------+-------+---+
| SecondaryContact | Name  | a |
+------------------+-------+---+
| SecondaryContact | Phone | b |
+------------------+-------+---+
| SecondaryContact | Email | c |
+------------------+-------+---+
| TechnicalContact | Name  | e |
+------------------+-------+---+
| TechnicalContact | Phone | f |
+------------------+-------+---+
| TechnicalContact | Email | g |
+------------------+-------+---+
| BillingContact   | Name  | m |
+------------------+-------+---+
| BillingContact   | Phone | n |
+------------------+-------+---+
| BillingContact   | Email | o |
+------------------+-------+---+

Just take away the [fn:contains(local-name(),"Contact")] and you will see the Name-Values of ProjectInfo and OtherStuff too.

If you need your columns side-by-side you might use PIVOT

SELECT p.*
FROM
(
    SELECT Level1.value('local-name(.)','nvarchar(max)') AS Level1_Name
          ,Level2.value('local-name(.)','nvarchar(max)') AS Level2_Name
          ,Level2.value('@value','nvarchar(max)') AS Level2_Value
    FROM @xml.nodes('/root/*[fn:contains(local-name(),"Contact")]') A(Level1)
    CROSS APPLY Level1.nodes('*') AS B(Level2)
) AS tbl
PIVOT
(
    MIN(Level2_Value) FOR Level2_Name IN(Name,Phone,Email)
) AS p;

The result:

+------------------+------+-------+-------+
| Level1_Name      | Name | Phone | Email |
+------------------+------+-------+-------+
| BillingContact   | m    | n     | o     |
+------------------+------+-------+-------+
| PrimaryContact   | x    | y     | z     |
+------------------+------+-------+-------+
| SecondaryContact | a    | b     | c     |
+------------------+------+-------+-------+
| TechnicalContact | e    | f     | g     |
+------------------+------+-------+-------+
Shnugo
  • 66,100
  • 9
  • 53
  • 114
  • Thanks for the detailed answer! This is what I was looking for. I just need to apply this across the board to the all the documents stored in an XML column instead of a single static document. – JNYRanger Jun 28 '16 at 14:11