One execution of your script will produce one computer name and a list of one or more monitors. You can pass these to a SQL Server stored procedure, you just have to get the parameter types correct.
The computer name is simplest. With just this the SP definition would look like this:
create procedure dbo.MyProcedureName
@ComputerName varchar(100)
as
...
I use varchar(100)
as an example. You use whatever's right for you. The list of monitors is more difficult because it may contain many values. For this you have to use a table-valued parameter and for that you will have to define a type:
create type MonitorList AS TABLE (
MonitorName varchar(100)
);
GO
Now the SP looks like this:
create procedure dbo.MyProcedureName
@ComputerName varchar(100),
@Monitors MonitorList READONLY
as
...
Now to the body of the SP. If you use the names as the primary key in tables Computer
and Monitor
the INSERT
statements are straightforward. Assuming you have primary keys and foreign keys defined -- you should -- you have to insert in the correct sequence to respect these key definitions i.e. into Computer
and Monitor
and only then into the mapping table.
insert dbo.Computer (ComputerName)
values (@ComputerName);
The type we defined, and variables created off it, behave just like any other table:
insert dbo.Monitor (MonitorName)
select MonitorName
from @Monitors;
insert Monitor_Computer_Map (ComputerName, MonitorName)
select @ComputerName, MonitorName
from @Monitors;
If your tables have surrogate keys that use IDENTITY
, however, you have more work to do. You have to capture these identity values as they are generated in the parent tables and use them in the mapping table. This can be done with the OUPUT
clause:
declare @ComputerID table ( ComputerID int);
insert dbo.Computer (ComputerName)
output INSERTED.ID
into @ComputerID
values (@ComputerName);
Do the same thing for monitors and use the local table variables to populate the mapping table.
Of course you want to have appropriate validation, duplicate checking and error handling in the body of the SP, too.
You don't say what scripting language you use. The documentation for it will tell you how to declare and populate stored procedure parameters for SQL Server.
Response to OP's EDIT #2:
First, a few tips. Please post the full error message; it helps immensely with debugging. Second, if you're using SSMS you can double-click an error and it will highlight the code in error. Third, get in the habit of closing your statements with a semicolon. It is not required yet but it will be soon.
If all computers have exactly one monitor then the TVP is not needed. You are correct. How many developers have only one monitor these days? I've seen finance traders' stations with eight. In these cases you do want a TVP. Please, please, please do not be tempted to write @Serial1, @Serial2, @Serial3 ...
.
Your code will throw the error Must declare the scalar variable "@MonitorId".
This is because of your third INSERT statement:
INSERT INTO dbo.Monitor_Computers (Monitor_Computer_Monitor, Monitor_Computer_Computer)
VALUES (@MonitorId, @ComputerId);
When you use the INSERT..VALUES
syntax SQL Server demands that there can be only one value per variable. You have provided table valued variables which could (potentially) hold many values. What you need is the INSERT..SELECT
syntax. Yes, I know @ComputerID
only has one row but it is a table and it could have many rows. What you need is:
INSERT INTO dbo.Monitor_Computers (Monitor_Computer_Monitor, Monitor_Computer_Computer)
SELECT m.Id, c.Id
from @ComputerId AS c
cross join @MonitorId AS m;
The three problems with your XQuery that I can see are (and none of this has anything to do with it being Typed XML):
You are not specifying the correct path to the /root
node. It should be:
SELECT t.c.value('(.)[1]','varchar(50)') as type
from @ixml.nodes('/WebService/NewLeads/OutputSchema/root/item/transaction') as t(c)
XML is case-sensitive so you need to use an upper-case "T" for the "Transaction" node:
SELECT t.c.value('(.)[1]','varchar(50)') as type
from @ixml.nodes('/WebService/NewLeads/OutputSchema/root/item/Transaction') as t(c)
While those fixes would probably get you 1 value back (in this case, it should be "SaleOnly"), it won't iterate through the "item" nodes because you were too specific in the path supplied to the .nodes()
function. Instead, the final node of that specification should be "item", which is what you want to iterate through. And in that case, you move the "Transaction" part up to the .value()
function:
SELECT t.c.value('(./Transaction)[1]','varchar(50)') as type
from @ixml.nodes('/WebService/NewLeads/OutputSchema/root/item') as t(c)
Regarding the statement of:
I work on xml both missing and containing second xmlns declaration in root element, as well as with structure trimmed down to just root.
The "structure trimmed down to just root" should be handleable by removing everything between the first /
and the /
right before /root...
(i.e. the WebService/NewLeads/OutputSchema
). So the resulting path would be:
from @ixml.nodes('//root/item') as t(c)
NOTE:
I am not able to get this to work 100% with the namespace declared in the <WebService>
element (please see additional notes as this is no longer the case). Taking that out makes it work. Giving it a prefix, such as xmlns:something="http://www.orbis-software.com/WebSvcCon"
makes it work, but then that needs to be declared in the methods. The only way I can get it to work right now is by declaring the default namespace in each XML function (.nodes
and .value
) as follows:
SELECT t.c.value('declare default element namespace "http://www.orbis-software.com/WebSvcCon";
(./Transaction)[1]','varchar(50)') as [type]
from @ixml.nodes('declare default element namespace "http://www.orbis-software.com/WebSvcCon";
/WebService/NewLeads/OutputSchema/root/item') as t(c)
NOTE 2:
Even better, you can use WITH XMLNAMESPACES
to declare one or more namespaces to use for the entire query, so no need to define in each XML function. The following both work:
;WITH XMLNAMESPACES (DEFAULT 'http://www.orbis-software.com/WebSvcCon')
SELECT t.c.value('(./Transaction)[1]','varchar(50)') as [type]
from @ixml.nodes('/WebService/NewLeads/OutputSchema/root/item') as t(c)
;WITH XMLNAMESPACES (DEFAULT 'http://www.orbis-software.com/WebSvcCon')
SELECT t.c.value('(./Transaction)[1]','varchar(50)') as [type]
from @ixml.nodes('//root/item') as t(c)
However, just keep in mind that if the document is missing the <WebService xmlns="http://www.orbis-software.com/WebSvcCon">
element and hence has no default namespace, then you need to remove the ;WITH XMLNAMESPACES
part. Of course, if the <root>
element has its own default namespace, then maybe you will need to keep it. You can play around with it until it works, now that you know the connection between these pieces.
NOTE 3:
If you do end up having two default namespaces declared -- one in the <WebService>
element and one in the <root>
element -- then you need to specify the URI noted in <root xmlns="bob">
and the //
syntax instead of the fully-qualified path. So if your XML looked like:
<WebService xmlns="http://www.orbis-software.com/WebSvcCon">
<NewLeads>
<OutputSchema>
<root xmlns="http://someplace" type="array">
You would then use:
;WITH XMLNAMESPACES (DEFAULT 'http://someplace')
SELECT t.c.value('(./Transaction)[1]','varchar(50)') as [type]
from @ixml.nodes('//root/item') as t(c)
But that won't help if you do have the <WebService>
element and yet the <root>
element is missing the xmlns
declaration. In that case you still need to specify the namespace noted in the <WebService>
element. Fun, fun, fun :-).
NOTE 4:
Even more better: incorporating something mentioned in @wBob's answer, we can actually get rid of the ;WITH XMLNAMESPACES
clause, and instead use a namespace wildcard. You just need to prefix every node of every XML function with *:
. Now the query should look as follows:
SELECT t.c.value('(./*:Transaction)[1]','varchar(50)') AS [type],
t.c.value('(./*:SaleProperty/*:PostDistrict)[1]','varchar(50)') AS [PostDistrict]
FROM @ixml.nodes('//*:root/*:item') t(c);
Doing this means that the query works in all of your scenarios:
Full structure starting with "WebService" node, second xmlns declaration:
<WebService xmlns="http://www.orbis-software.com/WebSvcCon">
<NewLeads>
<OutputSchema>
<root xmlns="uri" type="array">
Full structure starting with "WebService" node, single xmlns declaration:
<WebService xmlns="http://www.orbis-software.com/WebSvcCon">
<NewLeads>
<OutputSchema>
<root type="array">
Trimmed down structure starting with "root" node, single xmlns declaration:
<root xmlns="uri" type="array">
Best Answer
Are you familiar with sql:column and sql:variable? They allow you to manipulate XML with external variables or tables? Work through this demo and examine the different 'before' and 'after' results to see if they could help you: