June 19, 2009 at 3:17 pm
Hello,
I have goggled a couple different methods for importing using t-sql and found this one to suit my needs. However I am having some trouble. My problem is the format of the xml document is inconsistent. Some places are missing elements like Note in the first one, some places have duplicate element names like Note in the second and third, some are missing a element like Position in the fourth. My goal is to create a stored proc that creates/populates a table from scanning an xml document.
App id BaseVechileId Note Qty PartType Position Part
A 368424 30187 Bulk Pkg of 10 Orifice Tubes w/o Rear A/C 1 6936 22 6
A 368425 30187 Bulk Pkg of 25 Orifice Tubes w/o Rear A/C 1 6936 NULL 8
My questions are: 1) Would it be possible to scan the entire document and for each unique element make a column like the table above?
2) After making the table can I automatically insert the fields that it has searched ?
3) If there are duplicate elements like Note is there a way to concatenate them into one field ?
4) If the field does not exist can i automate it to set a value of NULL like for Position?
I know that it is not 100% but I feel like I'm almost there. The xml and sql statements are below. Please help.
_________________________
CREATE TABLE #WorkingTable
(Data XML)
INSERT INTO #WorkingTable
SELECT * FROM OPENROWSET (BULK 'C:\Documents and Settings\Xml-Position.xml', SINGLE_BLOB) AS data
DECLARE @XML AS XML, @hDoc AS INT
SELECT @XML = Data FROM #WorkingTable
EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML
SELECT *
FROM OPENXML(@hDoc, '//App', 1)
WITH (App VARCHAR(10) '@action', id VARCHAR(50) '@id',
BaseVehicleId VARCHAR(50)'BaseVehicle/@id',
Note VARCHAR(500) 'Note',
Qty VARCHAR(10) 'Qty',
PartType VARCHAR(10)'PartType/@id',
IF Position NOT NULL
BEGIN
Position VARCHAR(10)'Position/@id',
ELSE SET Position = NULL
END --I know this is not right but I gave it a shot
Part VARCHAR 'Part')
EXEC sp_xml_removedocument @hDoc
-Chris
June 19, 2009 at 3:36 pm
Hi Chris
Let me start with some formatting of your post to make it easier to read for me and other guys and gals here 🙂
Your expected Result:
App id BaseVechileId Note Qty PartType Position Part
A 368424 30187 Bulk Pkg of 10 Orifice Tubes w/o Rear A/C 1 6936 22 6
A 368425 30187 Bulk Pkg of 25 Orifice Tubes w/o Rear A/C 1 6936 NULL 8
Your XML:
<Root>
<App action="A" id="368423">
<BaseVehicle id="30187"/>
<Qty>1</Qty>
<PartType id="6768"/>
<Position id="22"/>
<Part>54863</Part>
</App>
<App action="A" id="368424">
<BaseVehicle id="30187"/>
<Note>Bulk Pkg of 10 Orifice Tubes</Note>
<Note>w/o Rear AC</Note>
<Qty>1</Qty>
<PartType id="6936"/>
<Position id="22"/>
<Part>638</Part>
</App>
<App action="A" id="368425">
<BaseVehicle id="30187"/>
<Note>Bulk Pkg of 25 Orifice Tubes</Note>
<Note>w/o Rear AC</Note>
<Qty>1</Qty>
<PartType id="6936"/>
<Part>8638</Part>
</App>
<App action="A" id="368426">
<BaseVehicle id="30190"/>
<Note>w/ Custom Center Console</Note>
<Qty>1</Qty>
<PartType id="6768"/>
<Part>54914</Part>
</App>
</Root>
... unfortunately the XML formatting on this site does not work correct always.
Your SQL:
CREATE TABLE #WorkingTable
(Data XML)
INSERT INTO #WorkingTable
SELECT * FROM OPENROWSET (BULK 'C:\Documents and Settings\Xml-Position.xml', SINGLE_BLOB) AS data
DECLARE @XML AS XML, @hDoc AS INT
SELECT @XML = Data FROM #WorkingTable
EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML
SELECT *
FROM OPENXML(@hDoc, '//App', 1)
WITH (App VARCHAR(10) '@action', id VARCHAR(50) '@id',
BaseVehicleId VARCHAR(50)'BaseVehicle/@id',
Note VARCHAR(500) 'Note',
Qty VARCHAR(10) 'Qty',
PartType VARCHAR(10)'PartType/@id',
IF Position NOT NULL
BEGIN
Position VARCHAR(10)'Position/@id',
ELSE SET Position = NULL
END --I know this is not right but I gave it a shot
Part VARCHAR 'Part')
EXEC sp_xml_removedocument @hDoc
Flo
June 19, 2009 at 3:48 pm
Let me tell you that you should avoid using sp_xml_preparedocument and sp_xml_removedocument. Both are marked as deprecated in SSE2k5 and much less powerful than newly introduced XML methods.
Does this fix your problem:
DECLARE @xml XML
SELECT @xml = 'Your XML here which does not appear correct here'
SELECT
T.C.value('@action', 'nvarchar(10)'),
T.C.value('@id', 'int'),
T.C.value('(BaseVehicle/@id)[1]', 'int'),
T.C.value('(Note)[1]', 'nvarchar(max)'),
T.C.value('(Qty)[1]', 'int'),
T.C.value('(PartType/@id)[1]', 'int'),
T.C.value('(Position/@id)[1]', 'int'),
T.C.value('(Part)[1]', 'int')
FROM @xml.nodes('Root/App') T(C)
Flo
June 19, 2009 at 3:59 pm
When you say newely introduced XML methods are you talking about using the import/export wizzard or something else ? I tried using the wizzard but was having trouble with it so I went to t-sql.
-Chris
June 19, 2009 at 4:05 pm
Nope. In SQL Server 2000 it was needed to use sp_xml_preparedocument to create a XML document in server memory and work with it.
In SQL Server 2005 new data-type XML was introduced and it supports some methods directly on it. Have a look to my statement and notice the "@xml.nodes" method call and the "T.C.value" method calls on returned columns.
June 19, 2009 at 4:13 pm
Flo,
I am referencing a .xml file that lives outside of sql in MyDocuments. Can you provide some xml code which you import that generates a table with unique columns and populates it using t-sql ?
Thanks,
-Chris
June 19, 2009 at 4:18 pm
This part of your posted statement is not marked as deprecated. 😀
Using OPENROWSET is the absolutely correct way:
DECLARE @xml XML
SELECT
@xml = BulkColumn
FROM OPENROWSET (BULK 'C:\Users\Flo\Temp\Test\test.xml', SINGLE_BLOB) AS data
SELECT
T.C.value('@action', 'nvarchar(10)'),
T.C.value('@id', 'int'),
T.C.value('(BaseVehicle/@id)[1]', 'int'),
T.C.value('(Note)[1]', 'nvarchar(max)'),
T.C.value('(Qty)[1]', 'int'),
T.C.value('(PartType/@id)[1]', 'int'),
T.C.value('(Position/@id)[1]', 'int'),
T.C.value('(Part)[1]', 'int')
FROM @xml.nodes('Root/App') T(C)
Flo
June 19, 2009 at 4:33 pm
What if you have multiple columns with the same name in the xml file. how can those be concatenated into one field and what if element lives in one app but not in another. Can you please provide sample xml ?
-Chris
June 19, 2009 at 4:42 pm
cthorn112 (6/19/2009)
What if you have multiple columns with the same name in the xml file. how can those be concatenated into one field
You cannot concatenate different node values into one row without some tricks. You can insert the data with all nodes into a temp table and concatenate in SQL.
and what if element lives in one app but not in another.
Have a look to your XML and the output of my statement. First App doesn't contain a note so it returned NULL for this column.
Can you please provide sample xml ?
Sample XML? Erm.. no, that's your part 😉
June 19, 2009 at 7:27 pm
You cannot concatenate different node values into one row without some tricks. You can insert the data with all nodes into a temp table and concatenate in SQL.
What sort of trick do you have up your sleeve for concatenating different node values into one row :hehe: ?
Have a look to your XML and the output of my statement. First App doesn't contain a note so it returned NULL for this column.
Once I get back to my office I will take a look a the output of your statement.
Sample XML? Erm.. no, that's your part
Fair Enough. I will clean up my code and make it easy to read on Monday.
Thanks for the quick responses and help Flo
-Chris
June 20, 2009 at 4:30 am
Hi Chris
cthorn112 (6/19/2009)
What sort of trick do you have up your sleeve for concatenating different node values into one row :hehe: ?
You can use a inline sub-query to query the note elements, join them over App-id and use another XML function (FOR XML PATH('')) to concatenate the text nodes:
DECLARE @xml XML
SELECT
@xml = BulkColumn
FROM OPENROWSET (BULK 'C:\Users\Flo\Temp\Test\test.xml', SINGLE_BLOB) AS data
SELECT
T.C.value('@action', 'nvarchar(10)'),
T.C.value('@id', 'int'),
T.C.value('(BaseVehicle/@id)[1]', 'int'),
(
SELECT
T2.C.value('(text())[1]', 'nvarchar(max)') + ','
FROM @xml.nodes('Root/App/Note') T2(C)
WHERE
T2.C.value('../@id', 'int') = T.C.value('@id', 'int')
FOR XML PATH('')
),
T.C.value('(Qty)[1]', 'int'),
T.C.value('(PartType/@id)[1]', 'int'),
T.C.value('(Position/@id)[1]', 'int'),
T.C.value('(Part)[1]', 'int')
FROM @xml.nodes('Root/App') T(C)
Sample XML? Erm.. no, that's your part
Fair Enough. I will clean up my code and make it easy to read on Monday.
Hope you didn't misunderstand. I cannot provide any sample XML since it isn't my business case ;-).
Thanks for the quick responses and help Flo
Always welcome.
Flo
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply