June 9, 2014 at 4:18 am
The requirement is to read XML element from database column.
The column looks like
<ClMetadataDataContract xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://www.cch.com/pfx.net/psi/">
<EntityType>1</EntityType>
<NameLine1>David Jones</NameLine1>
<NameLine2 />
<DefId />
<ClientID>jones</ClientID>
<ClientSubID />
<SortName />
<ClientType />
</ClMetadataDataContract>
And I need DefId from each row.
What is that I'm doing wrong in below query.
select N.C.value('(@EntityType)[1]','INT'),
N.C.value('(@defID)[1]','nvarchar(256)')
from schemaname.tablename
cross apply extmetadata.nodes('/ClMetadataDataContract') as N(C)
I have also tries OPENXML but no luck
Any help is appreciated
Thanks
June 9, 2014 at 4:38 am
This should get you started
😎
DECLARE @SXML XML = N'<ClMetadataDataContract xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://www.cch.com/pfx.net/psi/">
<EntityType>1</EntityType>
<NameLine1>David Jones</NameLine1>
<NameLine2 />
<DefId />
<ClientID>jones</ClientID>
<ClientSubID />
<SortName />
<ClientType />
</ClMetadataDataContract>';
DECLARE @TXML TABLE
(
TXML_ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL
,TXML_XML XML NOT NULL
);
INSERT INTO @TXML (TXML_XML)
VALUES (@SXML)
;WITH XMLNAMESPACES ( DEFAULT 'http://www.cch.com/pfx.net/psi/')
SELECT
TX.TXML_ID AS TXML_ID
,RO.OT.value('./EntityType[1]','INT') AS EntityType
,RO.OT.value('./NameLine1[1]','NVARCHAR(128)') AS NameLine1
,RO.OT.value('./NameLine2[1]','NVARCHAR(128)') AS NameLine2
,RO.OT.value('./DefId[1]','NVARCHAR(128)') AS DefId
,RO.OT.value('./ClientID[1]','NVARCHAR(128)') AS ClientID
,RO.OT.value('./ClientSubID[1]','NVARCHAR(128)') AS ClientSubID
,RO.OT.value('./SortName[1]','NVARCHAR(128)') AS SortName
,RO.OT.value('./ClientType[1]','NVARCHAR(128)') AS ClientType
FROM @TXML TX
OUTER APPLY TX.TXML_XML.nodes('/ClMetadataDataContract') AS RO(OT)
Results
TXML_ID EntityType NameLine1 NameLine2 DefId ClientID ClientSubID SortName ClientType
----------- ----------- ------------ ---------- ------ --------- ------------ --------- -----------
1 1 David Jones jones
June 9, 2014 at 4:55 am
Thanks much. It helped 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply