February 6, 2013 at 1:37 am
Dears,
i want read XML string in SQL 2008.Here the XML:
<InputColl>
<WorkItemInputInfo ID="00000000-0000-0000-0000-000000000000" ActName="ConfirmServiceFeesPayment">
<Texts>
<T Name="Notes" Value="" />
<T Name="WorkItemID" Value="15f8259a-1bb9-4c7d-83b8-92863741be81" />
<T Name="PaymentAttachmentID" Value="8be69b2c-bfed-4efe-bd6a-513256246fa9" />
</Texts>
<Files />
</WorkItemInputInfo>
<WorkItemInputInfo ID="00000000-0000-0000-0000-000000000000" ActName="LicensingVerifyRequest">
<Texts>
<T Name="IsVerfied" Value="True" />
<T Name="Notes" Value="" />
<T Name="WorkItemID" Value="259102a7-3b44-4f13-a968-0154eef8b74a" />
</Texts>
<Files />
</WorkItemInputInfo>
<WorkItemInputInfo ID="00000000-0000-0000-0000-000000000000" ActName="UploadExternalPartiesNotes">
<Texts>
<T Name="CivilDefenseCertificateAttachemtID" Value="ff888c5f-a081-4c0f-af3a-2c8d79c6021c" />
<T Name="CivilDefenserCertificatNumber" Value="213123123" />
<T Name="CivilDefenseExpiryDate" Value="05/04/35" />
<T Name="MunicipalityCertificateAttachemntID" Value="6f76f72a-6fa7-4cc8-8d6d-1725883ae8ef" />
<T Name="MunicipalityCertificateExpiryDate" Value="06/08/35" />
<T Name="MunicipalityCertificateNumber" Value="2131232131" />
<T Name="Notes" />
<T Name="WorkItemID" Value="3f70f80c-526a-47fd-97dd-c8dfa9ce4400" />
</Texts>
<Files />
</WorkItemInputInfo>
<WorkItemInputInfo ID="00000000-0000-0000-0000-000000000000" ActName="CheckExternalParties">
<Texts>
<T Name="Decision" Value="0" />
<T Name="Notes" Value="" />
<T Name="WorkItemID" Value="c4707409-5a3c-41ed-9f9e-b1ca1eaba1df" />
</Texts>
<Files />
</WorkItemInputInfo>
<WorkItemInputInfo ID="00000000-0000-0000-0000-000000000000" ActName="ApproveRequest">
<Texts>
<T Name="Decision" Value="0" />
<T Name="Notes" Value="" />
<T Name="WorkItemID" Value="999a9175-d5fe-4c87-84b9-75b1d420dea3" />
</Texts>
<Files />
</WorkItemInputInfo>
<WorkItemInputInfo ID="00000000-0000-0000-0000-000000000000" ActName="SelectInspector">
<Texts>
<T Name="InspectorUserID" Value="bd3f30c6-3ed4-421f-828e-30da9dd57b4d" />
<T Name="Notes" Value="" />
<T Name="WorkItemID" Value="1b95f7b1-1c4e-4212-8f7b-3009ba51acb4" />
</Texts>
<Files />
</WorkItemInputInfo>
<WorkItemInputInfo ID="00000000-0000-0000-0000-000000000000" ActName="ConfirmInspectorAssignment">
<Texts>
<T Name="IsApproved" Value="True" />
<T Name="Notes" Value="" />
<T Name="WorkItemID" Value="da794f19-de91-4aa2-bc89-f605ec1140fa" />
<T Name="InspectionFormID" Value="c4eb87d7-1e62-4f16-b805-aab63932f0d2" />
<T Name="ClassificationInspectorFacilityID" Value="2343522d-2c00-4e60-84ea-2efef71eb216" />
</Texts>
<Files />
</WorkItemInputInfo>
<WorkItemInputInfo ID="00000000-0000-0000-0000-000000000000" ActName="InspectionClassificationForm">
<Texts>
<T Name="ClassificationTypeID" Value="8" />
<T Name="Notes" Value="" />
<T Name="WorkItemID" Value="a61e2424-e185-4aa1-801b-ba34e0d28967" />
<T Name="ClassificationInspectorFacilityID" Value="42ff482b-eaa5-405e-a8bc-1b4d69ca0ef3" />
<T Name="InspectionFormID" Value="6c13a9dc-b57f-4c03-9cbd-0a5f96dbc15c" />
</Texts>
<Files />
</WorkItemInputInfo>
<WorkItemInputInfo ID="00000000-0000-0000-0000-000000000000" ActName="WriteInspectionReport">
<Texts>
<T Name="ClassificationMarksCount" Value="1010" />
<T Name="ClassificationID" Value="8" />
<T Name="Notes" Value="" />
<T Name="WorkItemID" Value="ceb40be9-9577-4f07-9ba5-a0da3f462345" />
<T Name="Status" Value="Submit" />
</Texts>
<Files />
</WorkItemInputInfo>
<WorkItemInputInfo ID="00000000-0000-0000-0000-000000000000" ActName="ApproveInspectionReport">
<Texts>
<T Name="Decision" Value="2" />
<T Name="Notes" Value="dsadsadsa" />
<T Name="WorkItemID" Value="776a5d94-d044-47c0-9d04-09f682b27c92" />
</Texts>
<Files />
</WorkItemInputInfo>
</InputColl>
Is there any help??
February 6, 2013 at 2:11 am
I found the answer as following:
DECLARE @idoc int
DECLARE @XML XML;
SET @XML = '<InputColl>
<WorkItemInputInfo ID="00000000-0000-0000-0000-000000000000" ActName="ConfirmServiceFeesPayment">
<Texts>
<T Name="Notes" Value="" />
<T Name="WorkItemID" Value="15f8259a-1bb9-4c7d-83b8-92863741be81" />
<T Name="PaymentAttachmentID" Value="8be69b2c-bfed-4efe-bd6a-513256246fa9" />
</Texts>
<Files />
</WorkItemInputInfo>
<WorkItemInputInfo ID="00000000-0000-0000-0000-000000000000" ActName="LicensingVerifyRequest">
<Texts>
<T Name="IsVerfied" Value="True" />
<T Name="Notes" Value="" />
<T Name="WorkItemID" Value="259102a7-3b44-4f13-a968-0154eef8b74a" />
</Texts>
<Files />
</WorkItemInputInfo>
<WorkItemInputInfo ID="00000000-0000-0000-0000-000000000000" ActName="UploadExternalPartiesNotes">
<Texts>
<T Name="CivilDefenseCertificateAttachemtID" Value="ff888c5f-a081-4c0f-af3a-2c8d79c6021c" />
<T Name="CivilDefenserCertificatNumber" Value="213123123" />
<T Name="CivilDefenseExpiryDate" Value="05/04/35" />
<T Name="MunicipalityCertificateAttachemntID" Value="6f76f72a-6fa7-4cc8-8d6d-1725883ae8ef" />
<T Name="MunicipalityCertificateExpiryDate" Value="06/08/35" />
<T Name="MunicipalityCertificateNumber" Value="2131232131" />
<T Name="Notes" />
<T Name="WorkItemID" Value="3f70f80c-526a-47fd-97dd-c8dfa9ce4400" />
</Texts>
<Files />
</WorkItemInputInfo>
<WorkItemInputInfo ID="00000000-0000-0000-0000-000000000000" ActName="CheckExternalParties">
<Texts>
<T Name="Decision" Value="0" />
<T Name="Notes" Value="" />
<T Name="WorkItemID" Value="c4707409-5a3c-41ed-9f9e-b1ca1eaba1df" />
</Texts>
<Files />
</WorkItemInputInfo>
<WorkItemInputInfo ID="00000000-0000-0000-0000-000000000000" ActName="ApproveRequest">
<Texts>
<T Name="Decision" Value="0" />
<T Name="Notes" Value="" />
<T Name="WorkItemID" Value="999a9175-d5fe-4c87-84b9-75b1d420dea3" />
</Texts>
<Files />
</WorkItemInputInfo>
<WorkItemInputInfo ID="00000000-0000-0000-0000-000000000000" ActName="SelectInspector">
<Texts>
<T Name="InspectorUserID" Value="bd3f30c6-3ed4-421f-828e-30da9dd57b4d" />
<T Name="Notes" Value="" />
<T Name="WorkItemID" Value="1b95f7b1-1c4e-4212-8f7b-3009ba51acb4" />
</Texts>
<Files />
</WorkItemInputInfo>
<WorkItemInputInfo ID="00000000-0000-0000-0000-000000000000" ActName="ConfirmInspectorAssignment">
<Texts>
<T Name="IsApproved" Value="True" />
<T Name="Notes" Value="" />
<T Name="WorkItemID" Value="da794f19-de91-4aa2-bc89-f605ec1140fa" />
<T Name="InspectionFormID" Value="c4eb87d7-1e62-4f16-b805-aab63932f0d2" />
<T Name="ClassificationInspectorFacilityID" Value="2343522d-2c00-4e60-84ea-2efef71eb216" />
</Texts>
<Files />
</WorkItemInputInfo>
<WorkItemInputInfo ID="00000000-0000-0000-0000-000000000000" ActName="InspectionClassificationForm">
<Texts>
<T Name="ClassificationTypeID" Value="8" />
<T Name="Notes" Value="" />
<T Name="WorkItemID" Value="a61e2424-e185-4aa1-801b-ba34e0d28967" />
<T Name="ClassificationInspectorFacilityID" Value="42ff482b-eaa5-405e-a8bc-1b4d69ca0ef3" />
<T Name="InspectionFormID" Value="6c13a9dc-b57f-4c03-9cbd-0a5f96dbc15c" />
</Texts>
<Files />
</WorkItemInputInfo>
<WorkItemInputInfo ID="00000000-0000-0000-0000-000000000000" ActName="WriteInspectionReport">
<Texts>
<T Name="ClassificationMarksCount" Value="1010" />
<T Name="ClassificationID" Value="8" />
<T Name="Notes" Value="" />
<T Name="WorkItemID" Value="ceb40be9-9577-4f07-9ba5-a0da3f462345" />
<T Name="Status" Value="Submit" />
</Texts>
<Files />
</WorkItemInputInfo>
<WorkItemInputInfo ID="00000000-0000-0000-0000-000000000000" ActName="ApproveInspectionReport">
<Texts>
<T Name="Decision" Value="2" />
<T Name="Notes" Value="dsadsadsa" />
<T Name="WorkItemID" Value="776a5d94-d044-47c0-9d04-09f682b27c92" />
</Texts>
<Files />
</WorkItemInputInfo>
</InputColl>'
CREATE TABLE #tbl(
[ActName] nvarchar(50) NOT NULL,
[Name] nvarchar(10) NOT NULL,
[Value] nvarchar(10) NULL)
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @XML
-- SELECT stmt using OPENXML rowset provider
INSERT INTO #tbl([ActName],[Name],[Value])
SELECT *
FROM OPENXML (@idoc, '/InputColl/WorkItemInputInfo/Texts/T',2)
WITH (ActName nvarchar(50) '../../@ActName',
Name nvarchar(10) '@Name',
Value nvarchar(10) '@Value')
SELECT * FROM #tbl
WHERE ActName = 'ApproveInspectionReport'
DROP TABLE #tbl
February 6, 2013 at 3:06 am
You can also use nodes
SELECT x1.r1.value('@ActName','NVARCHAR(50)') AS ActName,
x2.r2.value('@Name','NVARCHAR(10)') AS Name,
x2.r2.value('@Value','NVARCHAR(10)') AS Value
FROM @XML.nodes('/InputColl/WorkItemInputInfo') AS x1(r1)
CROSS APPLY x1.r1.nodes('Texts/T') AS x2(r2);
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537February 6, 2013 at 5:27 am
Thanks Mark-101232
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply