XML method for an attribute is getting NULL value in SQL Server 2012

Question

I want to extract a single value of attribute Name from node <Adapter>. However I am getting NULL values. Can someone help me here please?

Sample data:

<SqlSyncProviderScopeConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" IsTemplate="false">
  <Adapter Name="[Kit].[KIT_Kit]" GlobalName="[Kit].[KIT_Kit]" TrackingTable="[DataSync].[KIT_Kit_dss_tracking]" SelChngProc="[DataSync].[KIT_Kit_dss_selectchanges_9f5d350b-a9f7-48d2-8398-0a0d6de7595c]" SelRowProc="[DataSync].[KIT_Kit_dss_selectrow_9f5d350b-a9f7-48d2-8398-0a0d6de7595c]" InsProc="[DataSync].[KIT_Kit_dss_insert_9f5d350b-a9f7-48d2-8398-0a0d6de7595c]" UpdProc="[DataSync].[KIT_Kit_dss_update_9f5d350b-a9f7-48d2-8398-0a0d6de7595c]" DelProc="[DataSync].[KIT_Kit_dss_delete_9f5d350b-a9f7-48d2-8398-0a0d6de7595c]" InsMetaProc="[DataSync].[KIT_Kit_dss_insertmetadata]" UpdMetaProc="[DataSync].[KIT_Kit_dss_updatemetadata]" DelMetaProc="[DataSync].[KIT_Kit_dss_deletemetadata]" BulkTableType="[DataSync].[KIT_Kit_dss_BulkType_9f5d350b-a9f7-48d2-8398-0a0d6de7595c]" BulkInsProc="[DataSync].[KIT_Kit_dss_bulkinsert_9f5d350b-a9f7-48d2-8398-0a0d6de7595c]" BulkUpdProc="[DataSync].[KIT_Kit_dss_bulkupdate_9f5d350b-a9f7-48d2-8398-0a0d6de7595c]" BulkDelProc="[DataSync].[KIT_Kit_dss_bulkdelete_9f5d350b-a9f7-48d2-8398-0a0d6de7595c]" InsTrig="[Kit].[KIT_Kit_dss_insert_trigger]" UpdTrig="[Kit].[KIT_Kit_dss_update_trigger]" DelTrig="[Kit].[KIT_Kit_dss_delete_trigger]">
    <Col name="KitGuid" type="uniqueidentifier" size="16" param="@P_1" pk="true" />
    <Col name="KitHrid" type="nvarchar" size="50" param="@P_2" collation="SQL_Latin1_General_CP1_CI_AS" />
    <Col name="StudyGuid" type="uniqueidentifier" size="16" param="@P_3" />
    <Col name="StudyVersionGuid" type="uniqueidentifier" size="16" param="@P_4" />
    <Col name="StudyKitDefGuid" type="uniqueidentifier" size="16" param="@P_5" />
    <Col name="SiteGuid" type="uniqueidentifier" size="16" param="@P_6" />
    <Col name="SourceSystemName" type="nvarchar" size="50" param="@P_7" collation="SQL_Latin1_General_CP1_CI_AS" />
    <Col name="ReplacedByGuid" type="uniqueidentifier" size="16" null="true" param="@P_8" />
    <Col name="FirstScannedOn" type="datetimeoffset" size="10" null="true" param="@P_9" />
    <Col name="SiteType" type="nvarchar" size="50" null="true" param="@P_10" collation="SQL_Latin1_General_CP1_CI_AS" />
    <Col name="CtmOrderGuid" type="uniqueidentifier" size="16" null="true" param="@P_11" />
    <Col name="EarlyExpirationOn" type="date" size="3" null="true" param="@P_12" />
    <Col name="DiscardedOn" type="datetimeoffset" size="10" null="true" param="@P_13" />
    <Col name="CreatedOn" type="datetimeoffset" size="10" param="@P_14" />
    <Col name="CreatedById" type="nvarchar" size="300" null="true" param="@P_15" collation="SQL_Latin1_General_CP1_CI_AS" />
    <Col name="ModifiedOn" type="datetimeoffset" size="10" null="true" param="@P_16" />
    <Col name="ModifiedById" type="nvarchar" size="300" null="true" param="@P_17" collation="SQL_Latin1_General_CP1_CI_AS" />
    <Col name="JobId" type="uniqueidentifier" size="16" null="true" param="@P_18" />
  </Adapter>  
</SqlSyncProviderScopeConfiguration>

This is my query :

;with xmlnamespaces('http://www.w3.org/2001/XMLSchema' as ns,
                    'http://www.w3.org/2001/XMLSchema-instance' as nf)
select config_data.value('(/ns:SqlSyncProviderScopeConfiguration/ns:Adapter/@name)[1]','varchar(100)')
from tableA

Desired output:

[Kit].[KIT_Kit]

I am not able to figure out why output is coming NULL?

marc_s · Accepted Answer · 2020-02-08 21:01:49Z

2

Those two XML namespaces on the root node are not really relevant, and none of them is a default namespace (which is defined by using just the xmlns tag - no prefix provided) - they would only ever apply if the XML node used their respective prefix - none of those nodes in your sample are.

So you should be able to get the desired output using:

select 
    config_data.value('(/SqlSyncProviderScopeConfiguration/Adapter/@name)[1]','varchar(100)')
from tableA

There's no need to define XML namespace declarations, if they're not being used....

answered Feb 8, 2020 at 21:01

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

sam Over a year ago

Great. Thanks a lot.. :)

Collectives™ on Stack Overflow

XML method for an attribute is getting NULL value in SQL Server 2012

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