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.
由于[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:
t - sql代码:
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)
1 个解决方案
#1
2
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)
#1
2
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)