时间戳

首页 > 解决方案 > Parse XML with SQL Server 2016

xml - Parse XML with SQL Server 2016

问题描述

In the past when I had

<Article PubModel="Print-Electronic">
    <Journal>
        <ISSN IssnType="Electronic">1360-0443</ISSN>
        <JournalIssue CitedMedium="Internet">

or

<Journal>
    <ISSN IssnType="Print">2234-943X</ISSN>
    <JournalIssue CitedMedium="Print">
    <Volume>8</Volume>

I would pull the ISSN number in my stored procedure using

nref.value('Article[1]/Journal[1]/ISSN[1]','varchar(max)') ISSN,

Now I want to identify which type of ISSN it is but I'm unsure where to put the @Print or @Electronic.

I've tried 

nref.value('Article[1]/Journal[1]/ISSN[1]/@Electronic', 'varchar(max)') ISSN,

and

nref.value('Article[1]/Journal[1]/@Electronic', 'varchar(max)') ISSN,

Neither worked. I want to put the Print ISSN into the ISSN_Print field if there is one and the Electronic in the ISSN_Electronic field if there is one.

Thanks for the help

标签： xmlstored-proceduressql-server-2016

解决方案

I fudged your XML a bit to get a working example. But this should get you what you're looking for:

declare @x xml = '<Journal>
            <ISSN IssnType="Print">2234-943X</ISSN>
            <ISSN IssnType="Electronic">1360-0443</ISSN>
            <JournalIssue CitedMedium="Print"/>
            <Volume>8</Volume></Journal>';

select @x.value('(Journal/ISSN[@IssnType="Print"])[1]', 'varchar(40)'),
    @x.value('(Journal/ISSN[@IssnType="Electronic"])[1]', 'varchar(40)');

Specifically, instead of using an ordinal selector (like [1]), you'll be using a property selector instead.

推荐阅读