Partial Wildcard sucht in SQL server XML Spalten

Ich habe eine XML-Spalte in einer MS SQL server 2012-database, die ich brauche, um eine Abfrage zu erstellen, die die Knoten in eine Tabelle extrahiert. Hier ist ein core der XML-Struktur:

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

Ich versuche, eine Abfrage zu schreiben, die den Namen, das Telefon und die E-Mail von jedem Kontaktknoten erhält. Der XPath /root/*Contact/ ist leider nicht legal. Ich weiß, dass ich eine Abfrage schreiben kann, die eine Reihe von UNION kombiniert, um die Spalten zu merge, aber ich fühle mich wie es wohl eine einfachere Art und Weise gibt, wie ich mir derzeit nicht bewusst bin.

Gibt es eine Möglichkeit, Wildcards oder irgendeine Art von OR-Mechanismus zu verwenden, mit dem der Name, das Telefon und die E-Mail von jedem der * Kontaktknoten abgerufen werden können?

Anmerkung: Ich kann /root/*/Name nicht verwenden, da es noch andere Knoten gibt, die Namen als den nächsten inneren Knoten haben, die nicht für Kontakte sind, und Telefon & E-Mail sind beide optionale Felder.

Sie können sich dieses Arbeitsbeispiel anschauen:

 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); 

Das Ergebnis

 +------------------+-------+---+ | 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 | +------------------+-------+---+ 

Nehmen Sie einfach die [fn:contains(local-name(),"Contact")] und Sie sehen auch die OtherStuff von ProjectInfo und OtherStuff .

Wenn du deine Spalten nebeneinander brauchst, kannst du PIVOT benutzen

 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; 

Das Ergebnis:

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