I'm developing a stored procedure for SQL Server 2012 express and developer edition with latest service pack.
I want to parse an XML and insert its data into a table.
This is my XML
:
<Parent ParentId="1" ParentCodeId="ASDAS121" ParentLevelId="1">
<Child ParentId="1" ChildCode="GF342" ChildLevel="0" Position="1"/>
<Child ParentId="1" ChildCode="G1A42" ChildLevel="0" Position="2"/>
<Child ParentId="1" ChildCode="GFS42" ChildLevel="0" Position="3"/>
<Child ParentId="1" ChildCode="GF242" ChildLevel="0" Position="4"/>
<!-- N childs more -->
<Child ParentId="1" ChildCode="DF342" ChildLevel="0" Position="N"/>
</Parent>
This is my stored procedure code:
CREATE PROCEDURE [dbo].[InsertXML]
@xmlString NVARCHAR(MAX)
AS
BEGIN
DECLARE @xml xml
set nocount on
-- Convert string data into XML.
SET @xml = @XmlString
DECLARE @hDoc int
--Prepare input values as an XML document
exec sp_xml_preparedocument @hDoc OUTPUT, @xmlData
INSERT INTO [dbo].[AGGREGATIONS]
SELECT ID_AGGREGATION, CODE, CODE_LEVEL
FROM OPENXML(@hdoc, '/Parent', 1) WITH (ID_AGGREGATION bigint '@ParentId', CODE nvarchar(20) '@ParentCodeId', CODE_LEVEL tinyint '@ParentLevelId')
-- I don't know how to continue
INSERT INTO [dbo].[AGGREGATION_CHILDS]
SELECT ID_AGGREGATION, CODE, CODE_LEVEL
FROM OPENXML(@hdoc, '/Parent/Child', 1) WITH (ID_AGGREGATION bigint 'ParentId', CODE nvarchar(20) '@ChildCodeId', CODE_LEVEL tinyint '@ChildLevelId', POSITION int '@Position')
How can I loop through all children? I don't know how many children will be.
Tiada ulasan:
Catat Ulasan