How can i read from XML string in SQL 2008

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

  • 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

  • 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/61537
  • 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