How to Shred XML into Multiple Rows with duplicate nodes using SQL Server 2008 R2

Question

What I am trying to do is extract the phone numbers and create a record per phone number:

I am only getting a record for Used because of the [1] singleton.

Any help is much appreciated. Thanks

Department     PhoneNumber
--------------------------
Used           866-605-1976
Parts          877-733-0759
BodyShop       877-733-0753

T-SQL code:

DECLARE @Xml AS VARCHAR(MAX) = '<WidgetViewData>
<WidgetView>
    <Data>
        <widgetid>7LS5R48I2ZL5DET585H5</widgetid>
        <widgettype>Dynamic</widgettype>
        <partnerid>2</partnerid>
        <cr_domain>target.dealer.com</cr_domain>
        <cr_referrer>http://organic.search.com:8080/test/refer.jspx</cr_referrer>
        <cr_url>http://target.dealer.com:8080/test/debugtest.jsp</cr_url>
        <cr_href>http://target.dealer.com:8080/test/debugtest.jsp</cr_href>
        <userhostaddress>127.0.0.1</userhostaddress>
        <useragent>Mozilla/5.0 (compatible; MSIE 9.0; Windows NT 6.1; WOW64; Trident/5.0)</useragent>
        <cradmin>2013_08_02_1375489550401</cradmin>
        <jsession>2013_08_02_1375505899038</jsession>
        <anchorphonenumbers>
            <anchorPhoneNumber>
                <department>Used</department>
                <phoneNumber>
                    <areaCode>866</areaCode>
                    <countryCode>1</countryCode>
                    <extension></extension>
                    <nanpa>605-1976</nanpa>
                    <phoneNumber>866-605-1976</phoneNumber>
                </phoneNumber>
                <phoneType>dynamic</phoneType>
                <statusFlag>active</statusFlag>
                <targetAttribute>revu_used</targetAttribute>
                <widgetId>7LS5R48I2ZL5DET585H5</widgetId>
            </anchorPhoneNumber>
            <anchorPhoneNumber>
                <department>Parts</department>
                <phoneNumber>
                    <areaCode>877</areaCode>
                    <countryCode>1</countryCode>
                    <extension></extension>
                    <nanpa>733-0759</nanpa>
                    <phoneNumber>877-733-0759</phoneNumber>
                </phoneNumber>
                <phoneType>dynamic</phoneType>
                <statusFlag>active</statusFlag>
                <targetAttribute>revu_parts</targetAttribute>
                <widgetId>7LS5R48I2ZL5DET585H5</widgetId>
            </anchorPhoneNumber>
            <anchorPhoneNumber>
                <department>BodyShop</department>
                <phoneNumber>
                    <areaCode>877</areaCode>
                    <countryCode>1</countryCode>
                    <extension></extension>
                    <nanpa>733-0753</nanpa>
                    <phoneNumber>877-733-0753</phoneNumber>
                </phoneNumber>
                <phoneType>dynamic</phoneType>
                <statusFlag>active</statusFlag>
                <targetAttribute>revu_bodyshop</targetAttribute>
                <widgetId>7LS5R48I2ZL5DET585H5</widgetId>
            </anchorPhoneNumber>
        </anchorphonenumbers>
    </Data>
</WidgetView>
 </WidgetViewData>'

DECLARE @xXml AS XML
SET @xXML = CONVERT(XML, @Xml)

SELECT location.xpath.value   ('(/WidgetViewData/WidgetView/Data/anchorphonenumbers/anchorPhoneNumber/department)[1]', 'varchar(max)') AS Department
FROM @xXml.nodes('//anchorPhoneNumber') AS location(xpath)

Shadwell · Accepted Answer · 2013-08-05 19:32:08Z

SELECT
    t.c.value('department[1]', 'varchar(100)') AS Department,
    t.c.value('(phoneNumber/phoneNumber)[1]', 'varchar(30)') AS Phone
FROM @xXml.nodes('//anchorPhoneNumber') AS t(c)

To add some values from parent elements:

SELECT
    t.c.value('../../widgetid', 'char(20)') AS WidgetID,
    t.c.value('../../widgettype', 'varchar(50)') AS WidgetType,
    t.c.value('../../partnerid', 'varchar(10)') AS PartnerID,
    t.c.value('department[1]', 'varchar(100)') AS Department,
    t.c.value('(phoneNumber/phoneNumber)[1]', 'varchar(30)') AS Phone
FROM @xXml.nodes('//anchorPhoneNumber') AS t(c)

Here is the full final snippet for future reference:

SELECT t.xpath.value('../../partnerid[1]', 'int') AS PartnerId
 ,t.xpath.value('../../widgetid[1]', 'varchar(20)') AS WidgetId
 ,t.xpath.value('(department)[1]', 'varchar(100)') AS Department
 ,t.xpath.value('(phoneNumber/phoneNumber)[1]', 'varchar(25)') AS PhoneNumber
 ,t.xpath.value('(phoneType)[1]', 'varchar(25)') AS PhoneType
 ,t.xpath.value('(targetAttribute)[1]', 'varchar(100)') AS TargetAttribute
 ,t.xpath.value('../../cr_display_logo[1]', 'bit') AS DisplayQualityAssured
 ,t.xpath.value('../../cr_test[1]', 'bit') AS WidgetTest
 ,t.xpath.value('../../userhostaddress[1]', 'varchar(512)') AS UserHostAddress
 ,t.xpath.value('../../cr_domain[1]', 'varchar(100)') AS Domain
 ,t.xpath.value('../../cr_url[1]', 'varchar(MAX)') AS OriginalUrl
 ,t.xpath.value('../../cr_referrer[1]', 'varchar(MAX)') AS Referrer
 ,t.xpath.value('../../cr_search[1]', 'varchar(MAX)') AS Search
 ,t.xpath.value('../../cr_lastmodified[1]', 'varchar(50)') AS LastModified
 ,t.xpath.value('../../cr_clientdatetimenow[1]', 'varchar(50)') AS ClientDateTimeNow
 ,t.xpath.value('../../useragent[1]', 'varchar(MAX)') AS UserAgent
 ,t.xpath.value('../../cradmin[1]', 'varchar(256)') AS AdminCookie
 ,t.xpath.value('../../jsession[1]', 'varchar(256)') AS SessionCookie
FROM @xXml.nodes('//anchorPhoneNumber') AS t(xpath)

I see the three rows returned. Would you mind expanding on the xpath expression to include the rows listed above <anchorphonenumbers> such as widgetid, partnerid, etc.

Collectives™ on Stack Overflow

How to Shred XML into Multiple Rows with duplicate nodes using SQL Server 2008 R2

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related