How can i read from XML string in SQL 2008

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » How can i read from XML string in SQL 2008

How can i read from XML string in SQL 2008

Rate Topic

Display Mode

Topic Options

Author

Message

MsM

Posted Wednesday, February 06, 2013 1:37 AM

Forum Newbie

Group: General Forum Members
Last Login: Saturday, February 09, 2013 6:46 AM
Points: 3, Visits: 37

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??

Post #1416307

MsM

Posted Wednesday, February 06, 2013 2:11 AM

Forum Newbie

Group: General Forum Members
Last Login: Saturday, February 09, 2013 6:46 AM
Points: 3, Visits: 37

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

Post #1416322

Mark-101232

Posted Wednesday, February 06, 2013 3:06 AM

UDP Broadcaster

Group: General Forum Members
Last Login: Yesterday @ 2:28 PM
Points: 1,498, Visits: 18,142

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);

____________________________________________________

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Never approach a goat from the front, a horse from the rear, or a fool from any direction.

Post #1416354

MsM

Posted Wednesday, February 06, 2013 5:27 AM

Forum Newbie

Group: General Forum Members
Last Login: Saturday, February 09, 2013 6:46 AM
Points: 3, Visits: 37

Thanks Mark-101232

Post #1416433

« Prev Topic | Next Topic »

Permissions